We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Reseed temp autonumber before insert

Medium Priority
460 Views
Last Modified: 2013-11-10
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...

Comment
Watch Question

Racim BOUDJAKDJIDatabase Architect - Dba - Data Scientist
CERTIFIED EXPERT

Commented:

Author

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.
CERTIFIED EXPERT
Top Expert 2012

Commented:
>>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.
SQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

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 ...
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.