I am working on an insert that is taking a flat file and it splits into two tables for our accounting system.
The original file will look something like this...
ID,Desig1,Desig1Amount,Desig2,Desig2Amount,Desig3,Desig3Amount (up to 8 Designations)
The Batch tables
Result would be
BCHNUM, BCHSEQ, ID
BCHNUM BCHSEQ, DESIGSEQ, DESIG, DESIGAmount
Each person can have between 1 and 8 designations, each designation can only be designated to once by each person, if the person designates to the same place twice, the amounst should be added together and only one row inserted. the seed for desigseq must reset to 1 each time a new person is inserted and count up to 8 or the highest number of designations they have.
The only ideas i have is nesting cursors and reseeding my temp tables.
I must use t-sql for this. I am converting an ssis package that did this into t-sql due to restrictions on the ssis runtime. Lots of people will be running this at the same time.