Recently, I had the task of rebuilding a very large table in order to partition it and put it on a columnstore index. While testing this out in the lower environments, I quickly blew up the log and ran out of disk on the server, so I decided to batch it out instead. Below is an example of the query I used.
--create example source and target tables
SELECT TOP (101)ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS id
INTO #source
FROM sys.columns;
CREATE TABLE #target (id int);
--while data exists in the source, delete from source and insert into target
WHILE EXISTS (SELECT 1 FROM #source)
BEGIN
BEGIN TRAN;
DELETE TOP (10) FROM #source
OUTPUT DELETED.id
INTO #target (id);
COMMIT;
END
--review results
SELECT * FROM #source;
SELECT * FROM #target;
In the example, I set up a couple tables for the test. The WHILE loop checks if data exists in the source and then deletes a set of rows out and uses the OUTPUT clause to directly insert them into the new table.
This is a very lightweight operation because we do not need to check if the rows exist in the source table. We just delete a random set of rows until they are all moved. In reality, my batch size was 2 million - this should be adjusted accordingly.