Batch-wise INSERTs

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 330
While dumping huge data from one table to another, it would be better to do batch-wise inserts. It minimizes the error count in case of any failures...

This will end up with less number of failed records..


DECLARE @Table1 TABLE (Col1 INT)
DECLARE @Table2 TABLE (Col1 INT)

INSERT INTO @Table1 VALUES (1)
INSERT INTO @Table1 VALUES (3)
INSERT INTO @Table1 VALUES (5)
INSERT INTO @Table1 VALUES (6)
INSERT INTO @Table1 VALUES (7)
INSERT INTO @Table1 VALUES (8)
INSERT INTO @Table1 VALUES (9)

DECLARE @Count INT
DECLARE @StartNum INT

SET @StartNum = 1

SELECT @Count = COUNT(*) FROM @Table1

WHILE @StartNum<=@Count
BEGIN
WITH cte
AS
(
SELECT Col1,ROW_NUMBER() OVER (ORDER BY Col1) AS 'RN' FROM @Table1
)

INSERT INTO @Table2 SELECT Col1 FROM cte WHERE RN >= @StartNum AND RN < @StartNum+2

SET @StartNum += 2
Print 'Batch Number: '+ cast(@StartNum/2 as varchar(10))
WAITFOR DELAY '00:00:01'
END

SELECT * FROM @table1

SELECT * FROM @table2



Some part OutPut:

(2 row(s) affected)
Batch Number: 1

(2 row(s) affected)
Batch Number: 2

(2 row(s) affected)
Batch Number: 3

(1 row(s) affected)
Batch Number: 4

Comments or Responses

Login to post response