SELECT TOP N ... to get the TOP n rows from a query.
Is there anyway to do
SELECT TOP N SKIP N
SELECT MIDDLE N
or something to that effect? I am dealing with large data sets (20-50 million records) and would like to be able to query 50K records at a time and would like to find a solution that doesn't need an ORDER BY clause so the query stays fast. I also need to support closing the database connection and restarting the client application between 50K chunk.
Ok, I'll preface this by saying that it is dirty, low-down, high-smelling, and those are it's good points!
SELECT Cast(NULL AS INT) AS batchId, PK
DECLARE @c INT, @i INT
SET ROWCOUNT 50000
SELECT @i = 1, @c = 1
WHILE 0 < @c
UPDATE dbo.junque SET batchId = @i WHERE batchId IS NULL
SELECT @c = @@rowcount, @i = 1 + @i
SET ROWCOUNT 0
This should give you your primary keys divided into batches of an arbitrary size. You can then join back into your original table using the PK, working from the batch ids. Not perfect, but better than a poke in the eye with a sharp stick!