import data from one table to another table

Question

Hello everyone,

I am using SQL Server 2008 Enterprise. I need to import all data from Server/Instance "Server Foo", Database "Foo" and table "Foo", into destination Server/Instance "Server Goo", Database "Goo" and table "Goo". Table Foo and Table Goo are of the same schema. If the same row exists for table Goo, I want to keep the origin data in Goo and ingore the import row in Foo (table Foo and table Goo both has a uniqueidentifier type column called CustomerID which acts as primary key and clustered index), just like ignore duplicate key does.

I am looking for simple and reliable ways to write T-SQL to solve data export/import issue. Any reference samples?

EDIT 1:

I have tried the below solution using MERGE, but met with the following error from SQL Server Management Studio. Any ideas what is wrong?

Some more information:

LabTest1\SQLServer2008 => Server\Instance name; OrderDB => DB name; dbo => schema name; Orders => Table name.

merge into [dbo].[Orders] as Target
using "LabTest1\SQLServer2008.OrderDB.dbo.Orders" as source
on target.Hash = source.Hash
when not matched then
INSERT     ([Hash]
           ,[Order]
           ,[Name]
           ,[CreationTime]
           ,[Description])
     VALUES
     (
     source.[Hash], source.[Order], source.[Name], source.[CreationTime], source.[Description]
     )
when MATCHED then
;

Error message:

Msg 102, Level 15, State 1, Line 16 Incorrect syntax near ';'.

thanks in advance, George

Answer 1

If you are using the Enterprise edition of SQL Server then why make your project difficult for yourself by using raw T-SQL code?

You can achieve your objectives in a more simple and robust manner by using SQL Server Integration Services (SSIS), a technology specifically designed to perform ETL tasks and is of course included with your edition of SQL Server.

Pre-built components already exist to process data in the manner which you desire and you have the added bonus of being able to easily incorporate auditing and logging capabilities into your solution.

What you are looking to achieve could be accomplished via pure T-SQL coding, however you would be making life more difficult for yourself in the process and the resulting solution would, in my opinion, most likely be cumbersome and less elegant.