在 SQL Server 中的数据库之间复制行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4083304/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Copy rows between databases in SQL server
提问by Adele
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?
将大量行从一个数据库中的表复制到具有完全相同结构的不同数据库中的另一个表的最有效方法是什么？
回答by Chris J
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.
如果数据库位于不同的服务器上，您将需要考虑使用 OPENQUERY、OPENROWSET 或链接服务器之一来执行查询，但从根本上说，即使使用所有这些，您仍然需要编写上述命令的变体。
Alternatively, it's a case of BCP out and BCP in or using SQL Server Management Studio's data transfer wizard.
或者，这是 BCP 输出和 BCP 输入或使用 SQL Server Management Studio 的数据传输向导的情况。
回答by Remus Rusanu
If your log IO subsystem allows it, then:
如果您的日志 IO 子系统允许，则：
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 keptduring 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!).
但是让整个传输发生在一个单一的语句中意味着一个单一的事务，这意味着必须在整个语句期间生成和保留x2.5 数据大小的日志。IE。如果您传输 50Gb，您的目标数据库日志将增长到 250Gb（即使恢复模式设置为简单！）。
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.
如果您担心日志，那么您必须批量传输。您仍然可以执行 INSERT ... SELECT 技巧，但是您的 SELECT 必须使用一些键范围和批处理可接受的行数。
Ultimately, you can always do a bcp outfollowed by a bcp in, it will work pretty fast and is probably the fastest way to get a reliable transfer going.
回答by Nouman Bhatti
Insert into targetdatabase.dbo.tablename
select * from sourcedatabase.dbo.tablename
NOTE:.dbo is important
注意：.dbo 很重要
If the columns are different in source and target tables then explicitly mention the columns for both tables. Do keep in mind that data typesshould be same
如果源表和目标表中的列不同，则明确提及两个表的列。请记住，数据类型应该相同
回答by Justin King
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.
一个简单的方法是打开 SSMS 并右键单击数据库，然后转到任务 > 导入数据，然后按照向导设置源和目标。
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.
同时从目的地追加或清除数据。
回答by Amr Badawy
you can simply user Copy Database Wizard
http://www.packtpub.com/article/copying-database-sql-2008-copy-database-wizard
您可以简单地使用复制数据库向导
http://www.packtpub.com/article/copying-database-sql-2008-copy-database-wizard
回答by Mike Cheel
回答by Jaydeep Shil
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
插入到 CRS_New.dbo.SalesUpdateRBCustomerExternalRelationShipData_V1 (RBCustomerExternalEdgeKeyFather,RBCustomerExternalEdgeKeySon,EdgeLevelIndicator,Status,Edition,StatusValidFrom,ActiveIndicator,AddAttributeList_id) 选择 RBCustomerExternalEdgeKeyFather,RBCustomerExternalEdgeKeyFather,RBCustomerExternalEdgeKeySon,AddValidFromDataReferenceSon,EdgeListHistory,RBCustomerExternalEdgeKeySon,EdgeValidFromDataResStatus.RBCustomerExternalEdgeKeySon,EdgeListHeaders, RBCustomerExternalEdgeKeySon,EdgeListLevels, RBCustomerExternalEdgeKeySon,EdgeLevelIndicator,Status,Edition,StatusValidFrom,ActiveIndicator,AddAttributeList_id
