sql - 复制sql表的最快方法

Question

我正在寻找在我的 SQL Server 上复制表及其内容的最快方法，只需简单复制表，并将源和目标放在同一服务器/数据库上。

目前，使用存储过程 select * into sql 语句，复制超过 470 万条记录需要 6.75 分钟。这太慢了。

CREATE PROCEDURE [dbo].[CopyTable1]
AS
BEGIN
DECLARE @mainTable VARCHAR(255),
        @backupTable VARCHAR(255),
        @sql VARCHAR(255),
        @qry nvarchar(max);

SET NOCOUNT ON;

Set @mainTable='Table1'
Set @backupTable=@mainTable + '_Previous'
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@backupTable) AND type in (N'U'))
BEGIN
    SET @Sql = 'if exists (select * from sysobjects '
    SET @Sql = @Sql + 'where id = object_id(N''[' + @backupTable + ']'') and '
    SET @Sql = @Sql + 'OBJECTPROPERTY(id, N''IsUserTable'') = 1) ' + CHAR(13)
    SET @Sql = @Sql + 'DROP TABLE [' + @backupTable + ']'
    EXEC (@Sql)
END

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@mainTable) AND type in (N'U'))
    SET @Sql = 'SELECT * INTO dbo.[' + @backupTable + '] FROM dbo.[' + @mainTable + ']'
    EXEC (@Sql)
END

Answer 1

如果您担心速度，似乎您有两种选择；按 block 复制或 BCP/批量插入方法。

block 传输

DECLARE 
    @CurrentRow bigint, @RowCount bigint, @CurrentBlock bigint

SET 
    @CurrentRow = 1

SELECT 
    @RowCount = Count(*)
FROM 
    oldtable 
WITH (NOLOCK)

WHILE @CurrentRow < @RowCount
BEGIN
    SET 
        @CurrentBlock = @CurrentRow + 1000000

INSERT INTO 
    newtable 
(FIELDS,GO,HERE)
SELECT 
    FIELDS,GO,HERE
FROM (
    SELECT 
        FIELDS,GO,HERE, ROW_NUMBER() OVER (ORDER BY SomeColumn) AS RowNum 
    FROM 
        oldtable
    WITH (NOLOCK)
    ) AS MyDerivedTable
WHERE 
    MyDerivedTable.RowNum BETWEEN @startRow AND @endRow

SET 
    @CurrentRow = @CurrentBlock + 1
end

How to copy a huge table data into another table in SQL Server

BCP/批量插入

SELECT 
    * 
INTO 
    NewTable 
FROM 
    OldTable 
WHERE 
    1=2

BULK INSERT 
    NewTable 
FROM 
    'c:\temp\OldTable.txt' 
WITH (DATAFILETYPE  = 'native')

What is the fastest way to copy data from one table to another

http://www.databasejournal.com/features/mssql/article.php/3507171/Transferring-Data-from-One-Table-to-Another.htm