CODEWITHSUNDEEP
sql-serverdatabasedata-transfer
Randy
Randy

Reputation:

Transferring data from one database to another

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 what's 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.

Upvotes: 1

Views: 5640

Answers (3)

Aaron Alton
Aaron Alton

Reputation: 23236

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

Upvotes: 2

russau
russau

Reputation: 9108

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

Upvotes: 1

Mitch Wheat
Mitch Wheat

Reputation: 300797

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.

Upvotes: 1

Related Questions