考虑性能，如何在循环中将数据从一个表插入另一个表？

I need to insert data from one table to another in a loop with 10k rows in each loop run.

我需要在每个循环运行中以10k行的循环中将数据从一个表插入另一个表。

I want to try this because my insert into ***() select () from ### takes so much time as source table is having some millions of records.

我想尝试这个,因为我从###插入***()select()需要花费很多时间,因为源表有数百万条记录。

Will there be any improvement in performance by using this approach?

使用这种方法可以改善性能吗?

1 个解决方案

#1

You can try inserting in batches and see if there is any performance improvement. Here is the sample code:

您可以尝试分批插入并查看是否有任何性能改进。以下是示例代码:

DECLARE @Count INT
DECLARE @Start INT

SET @Start = 1
SELECT @Count = COUNT(*) FROM TableName1

WHILE @Start<=@Count
BEGIN
    WITH cte
    AS
    (
        SELECT Col1, Col2, Col3,ROW_NUMBER() OVER (ORDER BY Col1) AS 'RowNum' FROM TableName1
    )

    INSERT INTO TableName2 SELECT Col1, Col2, Col3 FROM cte WHERE RowNum >= @Start AND RowNum < @Start+10000

    SET @Start += 10000

    WAITFOR DELAY '00:00:10'
END

Here 10000 is the batch size and you can change this value as per your convenience.

这里10000是批量大小,您可以根据自己的方便更改此值。

#1