SQL Server: manipulating huge amount of data in single transaction

Question

I have a really huge table on SQL Server with ~1 billion rows, we need to migrate data from this table to another with such a simple code:

SET XACT_ABORT ON
BEGIN TRAN

INSERT INTO TargetTable
SELECT Col1, Col2, ... ColN 
FROM 
    HugeTable ht 
    INNER JOIN AnotherHugeTable aht ON aht.ID = ht.ColN
WHERE ColX IS NOT NULL

COMMIT TRAN

is it going to be OK to make it in single transaction? How would you go about it in this situation?

Answer 1

That would depend on the circumstances you are running it in and the business requirements.

Bear in mind, your transaction log will likely grow huge...(how often do you back up your TLog?)

If possible, split up into batches of say 10,000 (or 100,000).

If you have tables with literally billions of rows, you should investigate table partitioning (Requires Enterprise version of SQL Server).