CODEWITHSUNDEEP
sqlsql-server
Henley Wing Chiu
Henley Wing Chiu

Reputation: 22535

SQL server: Inserting a bunch of rows into a table, generating a "batch id" for every 100?

I'm inserting a bunch of rows into another table, and watch to generate an unique batch id for every X rows inserted (in this case X will be 100 or so).

So if I'm inserting 1000 rows, the first 100 rows will have batch_id = 1, the next 100 will have batch_id = 2, etc.

INSERT INTO BatchTable(batch_id, col1)
SELECT batchId, col1 //how to generate batchId???
FROM OtherTable

Upvotes: 1

Views: 2532

Answers (5)

Brock
Brock

Reputation: 266

I know this is an older question, but I ran into this same situation. In my case, however, introducing any kind of ORDER BY prevented parallelism and caused indexes to be missed or an expensive SORT step to be introduced to the query plan.

In my table, I have an integer unique key column (call it account_id) and I didn't care about the ordering of the batches or having an exact size of each individual batch, but I needed there to be batches numbered sequentially (say 1 - 100) broken up more-or-less evenly across the entire set of rows, so I did this instead:

INSERT INTO TABLE_1 (batch_id, account_id)
SELECT (account_id / 100) + 1, account_id 
FROM Account_Table

As time goes on and data grows, I may need to change the 100 to 1000 or 10000 or whatever, but for my case that was the easiest / cheapest way to accomplish the task.

Edit: Changed % (mod) to / (integer division), sorry for any confusion.

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280431

We can take advantage of integer division here for a simple way to round up to the next 100:

;WITH x AS 
(
  SELECT col1, rn = ROW_NUMBER() OVER (ORDER BY col1) FROM dbo.OtherTable
)
--INSERT dbo.BatchTable(batch_id, col1)
SELECT batch_id = (99+rn)/100, col1 FROM x;

When you're happy with the output, uncomment the INSERT...

Upvotes: 5

Kaf
Kaf

Reputation: 33839

Try this using row_number() function:

declare @batchGroup int = 100

Insert into BatchTable(batch_id, col1)
Select ((row_number() over (order by col1)-1)/@batchGroup)+ 1 As batch_id, col1
From OtherTable

Upvotes: 2

MartW
MartW

Reputation: 12538

INSERT INTO BATCHTABLE 
            (BATCH_ID, 
             COL1) 
SELECT 1 + ( ROW_NUMBER() 
               OVER( 
                 ORDER BY COL1) ) / 100 AS batchId, 
       COL1 
FROM   OTHERTABLE

Upvotes: 0

adrianm
adrianm

Reputation: 14726

INSERT INTO BatchTable (
    batch_id
  , col1
)
SELECT ((ROW_NUMBER() OVER (ORDER BY somecolumn)) - 1) / 100 + 1
      , col1 
FROM OtherTable

Upvotes: 1

Related Questions