Reseed temp autonumber before insert

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)
1233,A,10.00,B,10.00,C,10.00,D,20.00
1234  etc.
The Batch tables
Result would be
TABLE A
BCHNUM, BCHSEQ, ID
55555,1,1233
TABLE B
BCHNUM BCHSEQ, DESIGSEQ, DESIG, DESIGAmount
55555,1,1,A,10.00
55555,1,2,B,10.00
55555,1,3,C,10.00
55555,1,4,D,20.00
55555,2,1,A,10.00
etc
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.
Thank You...

ramos_ritaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
0
ramos_ritaAuthor Commented:
I know how to reseed the problem is how do I ROWNUMBER or AUTONUMBER based on a condition resetting... in this case i have a new donor and he has more than one designations that i have to insert bu their seed value.

I know there must be a way to do this with XML or WHILE loops or something that I can get it done.
0
Anthony PerkinsCommented:
>>I know how to reseed the problem is how do I ROWNUMBER <<
Then perhaps you should look at the ROW_NUMBER() function together with the PARTITION clause.  Unfortunately, I cannot help you any further as I am not following your comments at all.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Thinking out of the box:

you can insert the records from Flat file into one of your Temp table.
Once it is done, you can intercept the records for your two tables like this.

I have included columns ID,desig,desigamount only. Include your other columns like BCHNUM, BCHSEQ, ID over here during your processing and insert into your tables accordingly.

Kindly revert if I need to explain more on the logics.
select ID,desig,desigamount
from (
SELECT ID,Desig1 as desig, Desig1Amount as desigamount, 1 as DESIGSEQ
from urtable
union all
SELECT ID,Desig2, Desig2Amount, 2
from urtable
union all
SELECT ID,Desig3, Desig3Amount, 3
from urtable
union all
SELECT ID,Desig4, Desig4Amount, 4
from urtable
union all
SELECT ID,Desig5, Desig5Amount, 5
from urtable
union all
SELECT ID,Desig6, Desig6Amount, 6
from urtable
union all
SELECT ID,Desig7, Desig7Amount, 7
from urtable
union all
SELECT ID,Desig8, Desig8Amount, 8
from urtable ) temp
where desig is not null

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ramos_ritaAuthor Commented:
To me this seems more like a work around and would not work on any large scale (100's of rows across ... ) but absolutely worked in this case.  
in regards to the other comments on not understanding my issue.  
I guess maybe I didn't ask the question clearly ...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.