I have a SQL Table that we are selling off some of the data, so I need a quick way of splitting the main table into sub tables every 50 rows. Table nomenclature does not matter, I am just stuck on a way of selecting 50 different rows for each table with the table not holding a primary key or numeric field.

How would one go about this in SQL Server 2008?

  • You added some details in comments below that would be very helpful in describing your situation. You should add that information to your original question. – mathewb Nov 9 '17 at 19:00

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 IDENTITY column:

  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
  FROM myTable
 WHERE (((my_row_id - 1) / 50) + 1) = @batch_no

Since 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 batch_no.

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.

  • Just a suggestion on the IDENTITY option - you could simplify your logic by declaring my_row_id as IDENTITY(0, 1). Then you wouldn't need to offset by 1 in your calculation, and you'd still end up with fifty rows in your first batch. – mathewb Nov 9 '17 at 19:15
  • Good point = as long as my_row_id is considered disposable, we don't really need to start it at 1. In fact, IDENTITY(50,1) would simplify thing even more. – RDFozz Nov 9 '17 at 19:33

If it were greater amounts, I would have recommended partitions or something like that.

Since It's a small amount, I'd have one "Holding" Table that the records get created on (up to 50).

1) Records created into Holding table
2) @ 50 crecords in Holding table, Create new Historical Table 
2.5) Insert Records from Holding Table into newly created table
3) TRUNCATE Holding Table
4) ???
5) ProFit

I'd recommend creating the table name with a variable, so that you can use the same variable over and over.

Honestly I'm not really "for" this approach at all. Seems like too many tables and it would be difficult to manage. If it were me I'd keep it all in one table and have something like a BATCHID to separate the batches of 50 records.

  • I have full control over the SQL Table and could alter the table to update every 50 records to assign batch1, batch2, etc....just not sure of how to do such w/o having a numeric field in the table to use for the counter – BellHopByDayAmetuerCoderByNigh Nov 9 '17 at 17:31
  • Why can't use use a numeric field like INT or BIGINT? The procedure that Inserts would just check the MAX(BATCHID) and increment it by 1, right? – MguerraTorres Nov 9 '17 at 17:32
  • But how would I "group" the records from my holding table in batches of 50? – BellHopByDayAmetuerCoderByNigh Nov 9 '17 at 17:36
  • The holding tablewould have a max of 50 records. Every single record in the table would belong to the same BatchID when it gets pushed to History. Once they push to history, the Holding table gets truncated. – MguerraTorres Nov 9 '17 at 17:37
  • 1
    I think the question is: once you've pulled 50 records from SourceTable into the holding table and written them out, how do you avoid having any of those records from SourceTable show up in the second, third, fourth, etc. batches? Is there an outer cursor loop, writing individual records from SourceTable into the holding table one by one? – RDFozz Nov 9 '17 at 18:03

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.