如何批量插入SQL Server？

Question

我正在尝试批量将一张表的行插入到另一张表中。

DECLARE @batch INT = 10000;

WHILE @batch > 0
BEGIN
BEGIN TRANSACTION

    INSERT into table2
    select top (@batch) *
    FROM table1

    SET @batch = @@ROWCOUNT

COMMIT TRANSACTION

END

它运行在前10000个并插入它们。然后我收到错误消息“无法插入重复关键字”，因为它尝试插入相同的主键，所以我认为它正在尝试重复相同的批处理。我错过了什么逻辑来循环遍历批处理？这里可能有些简单，但我想不出来。

有人可以帮忙吗？谢谢

- Ryan Gadsdon

---

另一个选择是这个。 - SMor

Answer 1

您的代码一直在插入相同的行。您可以通过“分页”插入来避免此问题：

DECLARE @batch INT = 10000;
DECLARE @page INT = 0
DECLARE @lastCount INT = 1

WHILE @lastCount > 0
BEGIN
BEGIN TRANSACTION

    INSERT into table2
    SELECT col1, col2, ... -- list columns explicitly
    FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY YourPrimaryKey ) AS RowNum, *
              FROM      table1
            ) AS RowConstrainedResult
    WHERE   RowNum >= (@page * @batch) AND RowNum < ((@page+1) * @batch)
    SET @lastCount = @@ROWCOUNT
    SET @page = @page + 1

COMMIT TRANSACTION

END

- Sergey Kalinichenko

Answer 2

您需要一种方法来删除现有的行。看起来您已经有了一个主键，因此：

INSERT into table2
   SELECT TOP (@batch) *
   FROM table1 t1
   WHERE NOT EXISTS (SELECT 1 FROM table2 t2 WHERE t2.id = t1.id);

- Gordon Linoff

Answer 3

如何在这个问题中使用指定的关键词集：OFFSET - FETCH。

DECLARE @batch INT = 10000;
DECLARE @lastCount INT = 1;
DECLARE @totalCount INT = 0;

WHILE @lastCount > 0
BEGIN
    BEGIN TRANSACTION

    INSERT into table2
    select *
    FROM table1
    ORDER BY <id>
    OFFSET @totalCount ROWS
    FETCH NEXT @batch ROWS ONLY

    SET @lastCount = @@ROWCOUNT
    SET @totalCount += @lastCount

    COMMIT TRANSACTION

END

- Leonardo T.