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.