Solved

Reseed temp autonumber before insert

Posted on 2009-07-08
5
422 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...

0
Comment
Question by:ramos_rita
5 Comments
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24806490
0
 

Author Comment

by:ramos_rita
ID: 24807750
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24808760
>>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
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
ID: 24810156
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
 

Author Closing Comment

by:ramos_rita
ID: 31601241
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

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
issue installing SQL SERVER 2012 express on windows 7 9 36
SQL Login 17 37
Using Aggregate Functions to Count 3 32
Help Extract Specific in SQL 8 15
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now