How to Batch INSERT SQL Server?

Question

I am trying to batch inserting rows from one table to another.

DECLARE @batch INT = 10000;

WHILE @batch > 0
BEGIN
BEGIN TRANSACTION

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

    SET @batch = @@ROWCOUNT

COMMIT TRANSACTION

END

It runs on the first 10,000 and inserts them. Then i get error message "Cannot insert duplicate key" which its trying to insert the same primary key so i assume its trying to repeat the same batch. What logic am i missing here to loop through the batches? Probably something simple but i cant figure it out.

Can anyone help? thanks

Another alternative is this

SMor
– SMor

2020-01-24 16:17:38 +00:00
Commented Jan 24, 2020 at 16:17 — SMor
– SMor, Commented Jan 24, 2020 at 16:17

Sergey Kalinichenko · Accepted Answer · 2020-01-24 16:17:10Z

18

Your code keeps inserting the same rows. You can avoid it by "paginating" your inserts:

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

answered Jan 24, 2020 at 16:17

Sergey Kalinichenko

729k85 gold badges1.2k silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2020-01-24 16:06:11Z

6

You need some way to eliminate existing rows. You seem to have a primary key, so:

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

answered Jan 24, 2020 at 16:06

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Leonardo T. · Accepted Answer · 2023-10-06 08:05:08Z

1

How about using the designated set of keywords for this problem : 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

answered Oct 6, 2023 at 8:05

Leonardo T.

9710 bronze badges

1 Comment

Sandeep Suri Over a year ago

Look at this link : mssqltips.com/sqlservertip/5636/…

Collectives™ on Stack Overflow

How to Batch INSERT SQL Server?

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related