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?

Mitch Wheat · Answer 1 · 2012-05-11T05:27:29.083

4

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).

edited May 11 '12 at 05:27

answered May 11 '12 at 04:59

Mitch Wheat

295,962
43
465
541

our requirement is to migrate data and provide integrity – tartakynov May 11 '12 at 05:06
integrity: do you mean there are systems actively changing those tables? – Mitch Wheat May 11 '12 at 05:06
it's about data corruption during the migration – tartakynov May 11 '12 at 05:10
there are systems changing TargetTable but the source tables are out of usage and are static – tartakynov May 11 '12 at 05:16
Splitting the job into batches is definitely a good option under the circumstances you describe. – Daniel P May 11 '12 at 09:50

SQL Server: manipulating huge amount of data in single transaction

1 Answers1