create script to insert data from a table into another table

Question

I need to Insert data from a table to another table in different Database.

I have this Script to do that:

SELECT 'if not exists (select [PortSymbol] from dbo.Ports where [PortSymbol] =N''' + tbl.Change+ ''' )
    INSERT INTO dbo.Ports([PortSymbol],[HasMovementDiscount], [ModifyDateTime], [PortEnglishName], [PortName] ) 
        values(N'''+tbl.Change+''' , N''False'' , ''GetDate()'',N'''+tbl.EnglishName+''',N'''+tbl.Name+''' )  '
from
(
  SELECT top 100 [Change]
      ,[Name]
      ,[EnglishName] 
  FROM [dbo].[Ports$] where [Name] is not null 

 ) tbl

but this doesn't work correctly!

this is my output:

if not exists (select [PortSymbol] from dbo.Ports where [PortSymbol] =N'AD ' )
        INSERT INTO dbo.Ports([PortSymbol],[HasMovementDiscount], [ModifyDateTime], [PortEnglishName], [PortName] ) 
        values(N'AD ' , N'False' , 'GetDate()',N'',N'.ANDORRA' )
if not exists (select [PortSymbol] from dbo.Ports where [PortSymbol] =N'AD ALV' )
        INSERT INTO dbo.Ports([PortSymbol],[HasMovementDiscount], [ModifyDateTime], [PortEnglishName], [PortName] ) 
        values(N'AD ALV' , N'False' , 'GetDate()',N'Andorra l
if not exists (select [PortSymbol] from dbo.Ports where [PortSymbol] =N'AD CAN' )
        INSERT INTO dbo.Ports([PortSymbol],[HasMovementDiscount], [ModifyDateTime], [PortEnglishName], [PortName] ) 
        values(N'AD CAN' , N'False' , 'GetDate()',N'Canillo',
if not exists (select [PortSymbol] from dbo.Ports where [PortSymbol] =N'AD ENC' )
        INSERT INTO dbo.Ports([PortSymbol],[HasMovementDiscount], [ModifyDateTime], [PortEnglishName], [PortName] ) 
        values(N'AD ENC' , N'False' , 'GetDate()',N'Encamp',N

and schema of source table:

CREATE TABLE [dbo].[Ports$](
[Change] [nvarchar](255) NULL,
[Country] [nvarchar](255) NULL,
[Location] [nvarchar](255) NULL,
[Name] [nvarchar](255) NULL,
[EnglishName] [nvarchar](255) NULL,
[Subdivision] [nvarchar](255) NULL,
[Status] [nvarchar](255) NULL,
[Function] [nvarchar](255) NULL,
[Date] [nvarchar](255) NULL,
[IATA] [nvarchar](255) NULL,
[Coordinates] [nvarchar](255) NULL,
[Remarks] [nvarchar](255) NULL
) ON [PRIMARY]

and this is destination table:

CREATE TABLE [dbo].[Ports](
[PortID] [bigint] IDENTITY(1,1) NOT NULL,
[CityID] [bigint] NULL,
[CountryID] [bigint] NULL,
[PortName] [nvarchar](50) NOT NULL,
[PortEnglishName] [nvarchar](50) NOT NULL,
[PortSymbol] [nvarchar](6) NOT NULL,
[HasMovementDiscount] [bit] NOT NULL,
[ModifyDateTime] [datetime] NOT NULL,
 CONSTRAINT [PK_PORTS] PRIMARY KEY NONCLUSTERED 
(
    [PortID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Your question is unclear. Not clear what you want. What's dont work? R u getting any error or wrong output? brief it. — Dhwani
– Dhwani, Commented Jul 9, 2014 at 12:55
i want to create Script for all redcord of table like this: ` if not exists (select [PortSymbol] from dbo.Ports where [PortSymbol] =N'AD ' ) INSERT INTO dbo.Ports([PortSymbol],[HasMovementDiscount], [ModifyDateTime], [PortEnglishName], [PortName] ) values(N'AD ' , N'False' , 'GetDate()',N'',N'.ANDORRA' ) ` — Hamed Rahmani
– Hamed Rahmani, Commented Jul 9, 2014 at 12:59
but for some record create script like this: if not exists (select [PortSymbol] from dbo.Ports where [PortSymbol] =N'AD CAN' ) INSERT INTO dbo.Ports([PortSymbol],[HasMovementDiscount], [ModifyDateTime], [PortEnglishName], [PortName] ) values(N'AD CAN' , N'False' , 'GetDate()',N'Canillo', i have a wrong output. — Hamed Rahmani
– Hamed Rahmani, Commented Jul 9, 2014 at 13:01
Do you want to say that some record concatenate returned string values ? — ceth
– ceth, Commented Jul 9, 2014 at 13:07

Kiran Hegde · Accepted Answer · 2014-07-10 06:36:57Z

2

I think the problem with the query is, that if name or englishname columns contains ' then your output query becomes invalid. Also for getdate you should not use ''. Can you try the following query instead?

SELECT 'if not exists (select [PortSymbol] from dbo.Ports where [PortSymbol] =N''' + tbl.Change+ ''' )
    INSERT INTO dbo.Ports([PortSymbol],[HasMovementDiscount], [ModifyDateTime], [PortEnglishName], [PortName] ) 
        values(N'''+tbl.Change+''' ,'False', GetDate(),N'''+ REPLACE(tbl.EnglishName,'''','''''') +''',N'''+REPLACE(tbl.Name,'''','''''')+''' )  '
from
(
  SELECT top 100 [Change]
      ,[Name]
      ,[EnglishName] 
  FROM [dbo].[Ports$] where [Name] is not null 

 ) tbl

You have to do this for all the columns. You need to check the data type and remove the quote if they are not varchar based strings

edited Jul 10, 2014 at 6:36

answered Jul 9, 2014 at 13:09

Kiran Hegde

3,6811 gold badge18 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hamed Rahmani Over a year ago

this is output

if not exists (select [PortSymbol] from dbo.Ports where [PortSymbol] =N'AD ' )     INSERT INTO dbo.Ports([PortSymbol],[HasMovementDiscount], [ModifyDateTime], [PortEnglishName], [PortName] )          values(N'AD ' ,False, GetDate(),N'',N'.ANDORRA' )   if not exists (select [PortSymbol] from dbo.Ports where [PortSymbol] =N'AD ALV' )     INSERT INTO dbo.Ports([PortSymbol],[HasMovementDiscount], [ModifyDateTime], [PortEnglishName], [PortName] )          values(N'AD ALV' ,False, GetDate(),N'Andorra la Vel

but this is wrong!

Kiran Hegde Over a year ago

What is there in the record after 'Andorra la Vel'? Just another question, cant you use buit in generate script option to generate insert scripts? tech-recipes.com/rx/24277/…

Collectives™ on Stack Overflow

create script to insert data from a table into another table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related