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?
Add a comment
|
7 Answers
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.
1 Comment
HelpfulProgrammer
For other people like me that are new to this: yes, the double point (
sourcedatabase..sourcetable) is on purpose. It doesn‘t work if you just put one.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.
2 Comments
OMG Ponies
Is there any difference to
INSERT INTO ... SELECT ... vs SELECT ... INTO ...? Or is the latter just syntactic sugar? Besides the fact that the table can't already exist in SELECT ... INTO ....
Remus Rusanu
SELECT ... INTO ... is better, if possible. It will log significantly less if the database is in bulk logged mode. IS also faster because by definition the target is a heap w/o any NC indexes, so it will be the fastest possible insert. But can only be issue if the table does not exists, and OP mentioned the tables already exist.Insert into targetdatabase.dbo.tablename
select * from sourcedatabase.dbo.tablename
NOTE: .dbo is important
If the columns are different in source and target tables then explicitly mention the columns for both tables. Do keep in mind that data types should be same
3 Comments
Nouman Bhatti
:) I mentioned it mate... if your columns are different in source and target than you need to mention it, otherwise this will work
Nick.Mc
Even when they are the same you need to include them. Otherwise when you subsequently add a column to either the source or target it breaks.... which is even worse. Defensive programming
nuander
I like that this doesn't require a column list!
A simple way is to open SSMS and Right click on database and go to Tasks > Import Data and follow the wizard to set source and destination.
This way if data exists on different systems and even if you wish to change structure you can do.
Also append, or cleanout data at same time from destination.
Comments
I would consider a bulk insert:
Comments
you can simply user Copy Database Wizard
http://www.packtpub.com/article/copying-database-sql-2008-copy-database-wizard
Comments
insert into CRS_New.dbo.SalesUpdateRBCustomerExternalRelationShipData_V1 (RBCustomerExternalEdgeKeyFather,RBCustomerExternalEdgeKeySon,EdgeLevelIndicator,Status,Edition,StatusValidFrom,ActiveIndicator,AddAttributeList_id) select RBCustomerExternalEdgeKeyFather,RBCustomerExternalEdgeKeySon,EdgeLevelIndicator,Status,Edition,StatusValidFrom,ActiveIndicator,AddAttributeList_id from CRS.dbo.SalesUpdateRBCustomerExternalRelationShipData_V11
1 Comment
Jaydeep Shil
insert into DBName.dbo.TableName (col1,col2...) select (col1,col2...) from DBName.dbo.TableName
