Since you say you can change the original table:
- Alter original table, adding new column
batch_id (default NULL).
Run the following
DECLARE @rowCount int = 1
,@batchId int = 1
WHILE @rowCount > 0
UPDATE TOP (50) myTable
SET batch_id = @batchId
WHERE batch_id IS NULL
SELECT @rowCount = @@ROWCOUNT
,@batch_id = @batch_id + 1
- Then, simply create new tables
myTable_batch_x, where x is 1 through
@batchId - 1, and assigned records based on the stored batch IDs.
If you can't modify the original table, but have space to make a copy of it, create a copy with the new column, and proceed as above.
Alternately: add a
my_row_id column (integer) to the original table, as an
ALTER TABLE myTable
ADD COLUMN my_row_id int IDENTITY(1,1) NOT NULL;
This will create the new column, and automatically populate it with sequential integers starting from one.
Using the new value, assign rows to batches (1-50, 51-100, etc.). To get the rows that go into a given batch:
SELECT * -- or, actual column list - you can exclude my_row_id that way
WHERE (((my_row_id - 1) / 50) + 1) = @batch_no
my_row_id is an integer, if we divide it by another integer any fractional result will be dropped. So,
0/50 = 0, ...,
49/50 = 0,
50/50 = 1, .... To include
my_row_id = 50 in batch 1, we simply subtract 1 from
my_row_id. And, to start with
batch_no 1 instead of 0, we add one to get our
NOTE: As noted in the comments, you can simplify the code slightly. If you use
IDENTITY(50,0) when adding
my_row_id, then the WHERE clause could be:
WHERE (my_row_id / 50) = @batch_no
The first 50 rows would be numbered 50-99, and this equation would evaluate to
1 for them; for the next fifty rows (100-149), it evaluates to
2; and so on.