CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
George2
George2

Reputation: 45771

import data from one table to another table

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

Upvotes: 1

Views: 11842

Answers (3)

marc_s
marc_s

Reputation: 754488

In SQL Server 2008, you could script out your Goo.Goo table in SQL Server Mgmt studio and tell it to also create a script to insert all data by using T-SQL INSERT statements. Go the the Object Explorer, right-click on the database, pick "Tasks > Generate Scripts", pick the table you want to generate the data insert statements for, and make sure to use this option here:

alt text

Those could then be run on the other server to insert the table contents. In this case, however, you'd have to handle inserting possible existing rows yourself.

On the other hand, if both servers are on the same network, you could just use the "Linked Server" feature and link the source server to the target server and then use the SQL Server 2008 MERGE statement to import all the data from the source srever's table into the target server.

In the Object Explorer, go to "Server Objects", then "Linked Servers", right-click and "Add new linked server" to establish a connection between the two servers:

alt text

Once the servers are linked, a simple MERGE statement (new in SQL Server 2008) will allow you to merge the data from those two tables:

MERGE 
  INTO Goo.Goo as Target
  USING Foo.Foo.dbo.Foo as Source
  ON Source.ID = Target.ID
WHEN NOT MATCHED THEN
  INSERT (field1, field2, field3)
  VALUES (source.field1, source.field2, source.field3)  
WHEN MATCHED THEN
  -- do nothing
;

Read more about the new MERGE statement here:

or in the SQL Server 2008 Books Online.

Marc

Upvotes: 8

user8128167
user8128167

Reputation: 7676

For me when I had a similar problem, I just copied the data from the SQL Server Management Studio output window and pasted it into an Excel file, then imported the data into the other database using this tool:

http://www.codeproject.com/KB/database/Excel2Sql.aspx

Upvotes: 0

John Sansom
John Sansom

Reputation: 41819

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.

Upvotes: 1

Related Questions