SQL Server – Creating Tables Every X Rows

sql serversql-server-2008sql-server-2008-r2t-sql

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?

Best Answer

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
BEGIN
    UPDATE TOP (50) myTable
       SET batch_id = @batchId
     WHERE batch_id IS NULL
    ;
    SELECT @rowCount = @@ROWCOUNT
          ,@batch_id = @batch_id + 1
    ;
END;
  • 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:

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

Related Question