Sql-server – Log implications of copying data from one table to another

sql server

I need to move 10's of millions of rows from one table to another. Are there different ways I can do this that will minimize impact on log files? Clients sometimes choose Simple Recovery over the default Full. But even with simple recovery, bulk copy operations seem to consume huge amounts of log space. We can suggest a different recovery model if that will help. 

INSERT INTO Table2 (<columns>)
SELECT <columns>
FROM Table1
WHERE <condition>;

DELETE FROM Table1
WHERE <condition>;

COMMIT;

Many thanks in advance

Best Answer

But even with simple recovery, bulk copy operations seem to consume huge amounts of log space.

Your code use INSERT, not SELECT INTO. While both of them can be minimally logged in SIMPLE/BULK LOGGED recovery model, there is a difference:

SELECT INTO is always minimally logged and INSERT INTO requires some additional conditions to be minimally logged.

Your code surely produce fully logged operation as it does not use TABLOCK.

But TABLOCK is not the only requirement, it's only enough if you are inserting into a heap without indexes, but if your table has clustered index, data to be inserted should be ordered and a table should be empty. Other cases are summarized here: enter image description here

And here you can find The Data Loading Performance Guide

Related Question