Batch-wise INSERTs

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