SELECT TOP x DELETE TOP x for batch move?
Posted on 2011-04-20
I have a table with millions of rows and I need to run stored procedures to transform the data. 200K rows is the most SQL can reasonably handle (with the way the sp's are currently written.) I do not have any ID field in my table (DataTable below.) I want to MOVE batches of 200K records into another table, process them, and then go get another 200K.
My question is:
If I do ...
INSERT INTO WorkingTable (col1, col2, col3)
SELECT TOP 200000 val1, val2, val3 FROM DataTable
DELETE TOP 200000 FROM DataTable
Will this always delete the SAME rows I have just inserted into my WorkingTable? Or will TOP sometimes return a different set of rows. There is no ORDER BY statement, and the data coming in may not have a column that I can reliably sort on... not to mention the performance hit for the ORDER BY.