CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Hamed Rahmani
Hamed Rahmani

Reputation: 41

create script to insert data from a table into another table

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]

Upvotes: 0

Views: 961

Answers (1)

Kiran Hegde
Kiran Hegde

Reputation: 3681

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

Upvotes: 2

Related Questions