How to Auto-generate Order Line Item numbers for bulk uploads

 I had a problem where I had 17000 line items to insert into 9000 orders.

The system required line item numbers, preferably numbered 1through N where N could be anything from 1 to 200.

This is a tricky problem.  I found some code that can generate a number sequence without regard to Purchase order number, but not one that would number all the orders from 1 within that PO number.

This solution enabled me to run the inserts once per PO and catch all the orders.

--Go through each unique invoice and calculate the line numbers
declare commands cursor for 
select distinct 
	'declare @ID int; set @ID=0; update UploadedSales set @ID= LineNumber = @ID+1 where pono='''+PoNo+''';'
from UploadedSales

--loop thru the po numbers
declare @cmd varchar(max)


--run updates one by one.
open commands
fetch next from commands into @cmd
while @@FETCH_STATUS=0
begin
  exec(@cmd)
  fetch next from commands into @cmd
end

close commands
deallocate commands

There are 2 tricky parts to this:

• Executing a list of commands fetched from a table (a list of unique PoNos)
• Calculating an incrementing line number for each item being updated.

The first item is something I picked up off the Internet. How to create a series of commands and execute them one at time. For this I created a cursor for my SELECT DISTINCT command, and then looped through it.

To auto-generate the line numbers is tricky too. Note the part of the commands in the list generated by the first query. 'declare @ID int; set @ID=0; update UploadedSales set @ID= LineNumber = @ID+1 where pono='''+PoNo+''';'. This creates a command that looks like:

declare @ID int; set @ID=0; update UploadedSales set @ID= LineNumber = @ID+1 where pono='PONumber';'

Using this ID and updating with every insert is handled by the set @ID= LineNumber = @ID+1 part, a tricky way to both update the line with the next sequential number and increment it at the same time.