Log implications of copying data from one table to another 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 (&lt;columns&gt;)
SELECT &lt;columns&gt;
FROM Table1
WHERE &lt;condition&gt;;

DELETE FROM Table1
WHERE &lt;condition&gt;;

COMMIT;


Many thanks in advance
Even with Simple Recovery Model, you still have to log every part of the insert in case it fails and you need to rollback. If you are moving all of the rows (or almost all of them) as a one off process you may be better off just renaming the table and then altering the columns to be what you need.

If this is a process that runs over and over again (E.g. an end of month process) then you might want to look at partition switching.

If neither of those work, then you'll probably be better off batching your inserts. Michael Swart has a good blog on how to do that. See also Take Care When Scripting Batches.