Transfering data from one database to another

Question

I have two databases, lets say Database A and B, with different datastructures. In Database A there is a table contacts. In Database B there is a table Accounts.

I want to transfer data from Database A table contacts to Database B table Accounts. I am use SQL Server 2005 and want to write sql scripts to do this.

Can someone tell me whats the easiest and most efficient way to achieve this: a) If they are on the same SQL server b) If they are on different SQL servers.

Cheers

Answer 1

Use SSIS. It will work for both local and remote cases, and will enable you to set up a transform between the tables, to map columns to lother columns etc.

Answer 2

Is it a one off transfer? If it's a simple transfer I write a SELECT statement to create the INSERT statements, i.e.

SELECT 'INSERT INTO Accounts (ID, Name) VALUES (' + CAST(ID as VARCHAR) + ', ''' + Name  + ''')'
FROM Contacts

Run this on Database A - and it will spit out the all INSERT statements, which you copy and paste so you can run them on Database B.

Or, on the same database:

INSERT INTO DatabaseA.dbo.Accounts (ID, Name) 
SELECT Id, Name
FROM DatabaseB.dbo.Contacts

Still not happy - try setting up linked servers: http://msdn.microsoft.com/en-us/library/aa213778(SQL.80).aspx

Answer 3

The easiest method isn't necessarily the most efficient. SSIS is likely the most efficient, as Mitch already indicated.

The easiest (if you don't know SSIS already) is to just set up a linked server to the remote DB, and SELECT the data across using four-part naming. You set up a linked server using sp_addlinkedserver and sp_addlinkedsrvlogin (check BOL for the syntax), and query as follows:

INSERT INTO MyLocalTable (ColumnList)
SELECT <Columns> FROM RemoteServer.RemoteDB.RemoteSchema.RemoteTable