SQL Server中如何将一张大表数据复制到另一个表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5296106/
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
How to copy a huge table data into another table in SQL Server
提问by sqlchild
I have a table with 3.4 million rows. I want to copy this whole data into another table.
我有一个有 340 万行的表。我想将整个数据复制到另一个表中。
I am performing this task using the below query:
我正在使用以下查询执行此任务：
select *
into new_items
from productDB.dbo.items
I need to know the best possible way to do this task.
我需要知道完成这项任务的最佳方式。
采纳答案by Stephen Chung
If you are copying into a newtable, the quickest way is probably what you have in your question, unlessyour rows are very large.
如果您要复制到新表中，最快的方法可能是您的问题，除非您的行非常大。
If your rows are very large, you may want to use the bulk insert functions in SQL Server. I think you can call them from C#.
如果您的行非常大，您可能需要使用 SQL Server 中的批量插入功能。我认为您可以从 C# 调用它们。
Or you can first download that data into a text file, then bulk-copy (bcp) it. This has the additional benefit of allowing you to ignore keys, indexes etc.
或者您可以先将该数据下载到一个文本文件中，然后批量复制 (bcp) 它。这还有一个额外的好处，就是允许你忽略键、索引等。
Also try the Import/Export utility that comes with the SQL Management Studio; not sure whether it will be as fast as a straight bulk-copy, but it should allow you to skip the intermediate step of writing out as a flat file, and just copy directly table-to-table, which might be a bit faster than your SELECT INTOstatement.
还可以尝试 SQL Management Studio 附带的导入/导出实用程序；不确定它是否会像直接批量复制一样快，但它应该允许您跳过作为平面文件写出的中间步骤，而只是直接复制表到表，这可能比你的SELECT INTO陈述。
回答by Mathieu Longtin
I had the same problem, except I have a table with 2 billion rows, so the log file would grow to no end if I did this, even with the recovery model set to Bulk-Logging:
我遇到了同样的问题，除了我有一个包含 20 亿行的表，所以如果我这样做，即使将恢复模型设置为 Bulk-Logging，日志文件也会无限增长：
insert into newtable select * from oldtable
So I operate on blocks of data. This way, if the transfer is interupted, you just restart it. Also, you don't need a log file as big as the table. You also seem to get less tempdb I/O, not sure why.
所以我对数据块进行操作。这样，如果传输中断，您只需重新启动它。此外，您不需要像表一样大的日志文件。您似乎也获得了较少的 tempdb I/O，不知道为什么。
set identity_insert newtable on
DECLARE @StartID bigint, @LastID bigint, @EndID bigint
select @StartID = isNull(max(id),0) + 1
from newtable
select @LastID = max(ID)
from oldtable
while @StartID < @LastID
begin
set @EndID = @StartID + 1000000
insert into newtable (FIELDS,GO,HERE)
select FIELDS,GO,HERE from oldtable (NOLOCK)
where id BETWEEN @StartID AND @EndId
set @StartID = @EndID + 1
end
set identity_insert newtable off
go
You might need to change how you deal with IDs, this works best if your table is clustered by ID.
您可能需要更改处理 ID 的方式，如果您的表按 ID 进行聚类，这将最有效。
回答by earlxtr
I have been working with our DBA to copy an audit table with 240M rows to another database.
我一直在与我们的 DBA 合作将一个 240M 行的审计表复制到另一个数据库。
Using a simple select/insert created a huge tempdb file.
使用简单的选择/插入创建了一个巨大的 tempdb 文件。
Using a the Import/Export wizard worked but copied 8M rows in 10min
使用导入/导出向导工作但在 10 分钟内复制了 800 万行
Creating a custom SSIS package and adjusting settings copied 30M rows in 10Min
创建自定义 SSIS 包并调整设置在 10 分钟内复制了 3000 万行
The SSIS package turned out to be the fastest and most efficent for our purposes
事实证明 SSIS 包对我们的目的来说是最快和最有效的
Earl
伯爵
回答by user489998
Here's another way of transferring large tables. I've just transferred 105 million rows between two servers using this. Quite quick too.
这是传输大表的另一种方法。我刚刚使用它在两台服务器之间传输了 1.05 亿行。也挺快的。
- Right-click on the database and choose Tasks/Export Data.
- A wizard will take you through the steps but you choosing your SQL server client as the data source and target will allow you to select the database and table(s) you wish to transfer.
- 右键单击数据库并选择Tasks/Export Data。
- 向导将带您完成这些步骤，但您选择 SQL 服务器客户端作为数据源和目标将允许您选择要传输的数据库和表。
For more information, see https://www.mssqltips.com/sqlservertutorial/202/simple-way-to-export-data-from-sql-server/
有关详细信息，请参阅https://www.mssqltips.com/sqlservertutorial/202/simple-way-to-export-data-from-sql-server/
回答by Eric Isaacs
If it's a 1 time import, the Import/Export utility in SSMS will probably work the easiest and fastest. SSIS also seems to work better for importing large data sets than a straight INSERT.
如果是 1 次导入，SSMS 中的导入/导出实用程序可能会最简单、最快地运行。SSIS 似乎也比直接插入更适合导入大型数据集。
BULK INSERT or BCP can also be used to import large record sets.
BULK INSERT 或 BCP 也可用于导入大型记录集。
Another option would be to temporarily remove all indexes and constraints on the table you're importing into and add them back once the import process completes. A straight INSERT that previously failed might work in those cases.
另一种选择是临时删除您要导入的表上的所有索引和约束，并在导入过程完成后将它们添加回来。在这些情况下，以前失败的直接 INSERT 可能会起作用。
If you're dealing with timeouts or locking/blocking issues when going directly from one database to another, you might consider going from one db into TEMPDB and then going from TEMPDB into the other database as it minimizes the effects of locking and blocking processes on either side. TempDB won't block or lock the source and it won't hold up the destination.
如果您在直接从一个数据库到另一个数据库时处理超时或锁定/阻塞问题，您可以考虑从一个数据库进入 TEMPDB，然后从 TEMPDB 进入另一个数据库，因为它最大限度地减少了锁定和阻塞进程对任何一边。TempDB 不会阻止或锁定源，也不会阻止目标。
Those are a few options to try.
这些是一些可以尝试的选项。
-Eric Isaacs
-埃里克·艾萨克
回答by user10198537
Simple Insert/Select sp's work great until the row count exceeds 1 mil. I've watched tempdb file explode trying to insert/select 20 mil + rows. The simplest solution is SSIS setting the batch row size buffer to 5000 and commit size buffer to 1000.
在行数超过 1 百万之前，简单插入/选择 sp 的效果很好。我看到 tempdb 文件在尝试插入/选择 20 百万 + 行时爆炸。最简单的解决方案是 SSIS 将批处理行大小缓冲区设置为 5000，将提交大小缓冲区设置为 1000。
回答by DB Bee
If your focus is Archiving (DW) and are dealing with VLDB with 100+ partitioned tables and you want to isolate most of these resource intensive work on a non production server (OLTP) here is a suggestion (OLTP -> DW)
1) Use backup / Restore to get the data onto the archive server (so now, on Archive or DW you will have Stage and Target database)
2) Stage database: Use partition switch to move data to corresponding stage table
3) Use SSIS to transfer data from staged database to target database for each staged table on both sides
4) Target database: Use partition switch on target database to move data from stage to base table
Hope this helps.
如果您的重点是存档 (DW) 并且正在处理具有 100 多个分区表的 VLDB，并且您希望在非生产服务器 (OLTP) 上隔离大部分这些资源密集型工作，这里是一个建议 (OLTP -> DW) 1) 使用备份/恢复将数据放到存档服务器上（所以现在，在存档或 DW 上，您将拥有舞台和目标数据库）2) 舞台数据库：使用分区开关将数据移动到相应的舞台表
3) 使用 SSIS 从中传输数据阶段数据库到目标数据库两侧每个阶段表 4）目标数据库：使用目标数据库上的分区开关将数据从阶段移动到基表希望这会有所帮助。
回答by TomTom
select * into new_items from productDB.dbo.items
select * into new_items from productDB.dbo.items
That pretty much is it. THis is the most efficient way to do it.
差不多就是这样。这是最有效的方法。
