1

I have two tables with same structures in a MS Sql server : T1 and T2, T1 has around 4 Billion rows and T2 is an empty table. Need to insert T1 data in to T2 in batches, After each Batch Insert in to T2 from T1, I have to Delete that data(batch Delete) from T1. FYI: Tables contains 1 Primary Key and 4 non-clustered indexes

Please suggest me the best options/best query.

1

You can try this query to get good performance without using temp table or joining to delete from T1. And it's very simple.

SET ROWCOUNT 10000

WHILE EXISTS (SELECT TOP 1 'EXISTS' FROM T1 WHERE COLUMN=VALUE)
BEGIN
WAITFOR DELAY '00:00:10' -- You can pass it if you want interval between next delete
DELETE FROM T1
OUTPUT DELETED.* INTO T2
WHERE COLUMN=VALUE
END

SET ROWCOUNT 0

You can comment or remove WHERE clause if not required.

Thanks

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.