SQL Server中如何将一张大表数据复制到另一个表中 [英] How to copy a huge table data into another table in SQL Server

问题描述

我有一个包含 340 万行的表.我想将整个数据复制到另一个表中.

I have a table with 3.4 million rows. I want to copy this whole data into another table.

我正在使用以下查询执行此任务:

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.

推荐答案

如果您要复制到新表中，最快的方法可能是您的问题中的内容，除非 你的行非常大.

If you are copying into a new table, the quickest way is probably what you have in your question, unless your rows are very large.

如果您的行非常大，您可能需要使用 SQL Server 中的批量插入功能.我认为你可以从 C# 调用它们.

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#.

或者您可以先将该数据下载到一个文本文件中，然后对其进行批量复制 (bcp).这还有一个额外的好处，就是允许您忽略键、索引等.

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.

还可以尝试 SQL Management Studio 附带的导入/导出实用程序；不确定它是否会像直接批量复制一样快，但它应该允许您跳过作为平面文件写出的中间步骤，而只是直接复制表到表，这可能比你的 SELECT INTO 语句.

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 INTO statement.

这篇关于SQL Server中如何将一张大表数据复制到另一个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！