Copy rows between databases in SQL server

Question

What's the most efficient way to copy a large amount of rows from a table in one database to another table in a different database that has the exact same structure?

Answer 1

If the databasesare on the same server, then it's trivial - you'd do it as if you were copying between tables in the same database, i.e.:

INSERT INTO targetdatabase..targettable (col1, col2, col3, col4)     SELECT col1, col2, col3, col4 FROM sourcedatabase..sourcetable 

If the databases are on different servers, you'll need to look at using one of OPENQUERY, OPENROWSET or linked servers to do the query, but fundamentally even with all these, you'd still be writing a variation on the command above.

Alternatively, it's a case of BCP out and BCP in or using SQL Server Management Studio's data transfer wizard.

Answer 2

If your log IO subsystem allows it, then:

INSERT INTO target(field1, field2, field3) SELECT field1, field2, field3 FROM source; 

But having the entire transfer occur in one single statement means one single transaction, which means that x2.5 data size log has to be generated and kept during the entire statement. Ie. if you transfer 50Gb, your target db log will grow to 250Gb (even if the recovery mode is set to simple!).

If you are concerned about log, then you have to transfer in batches. You can still do the INSERT ... SELECT trick, but your SELECT has to use some key range and batch acceptable number of rows.

Ultimately, you can always do a bcp out followed by a bcp in, it will work pretty fast and is probably the fastest way to get a reliable transfer going.