Insert with sequence number for each unique id

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Insert with sequence number for each unique id

Author

Topic

ttran
Starting Member

23 Posts

Posted - 2006-10-02 : 17:26:14

Is it possible to insert records into a table with sequence number for each unique id? I want to insert into table-2 from table-1 but I don't know how to keep track of the sequence number on the table-2.
Thank you for any help.

table-1 (id is primary key)
id subj1 subj2 subj3 subj4
1 ab ac ad ae
2 bc bd
3 cd ca cb

table-2 (id + seq is primary key)
id seq subj
1 1 ab
1 2 ac
1 3 ad
1 4 ae
2 1 bc
2 2 bd
3 1 cd
3 2 ca
3 3 cb

ttran

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-10-02 : 17:58:35

SELECT id, 1 as seq, subj1
FROM table-1
WHERE subj1 IS NOT NULL
UNION
SELECT id, 2 as seq, subj2
FROM table-1
WHERE subj2 IS NOT NULL
UNION
SELECT id, 3 as seq, subj3
FROM table-1
WHERE subj3 IS NOT NULL
UNION
SELECT id, 4 as seq, subj4
FROM table-1
WHERE subj4 IS NOT NULL

ttran
Starting Member

23 Posts

Posted - 2006-10-02 : 19:42:20

Thank you for your help.
But if table-1 has up to 20 subj then I have to repeat the coding 20 times. I was hoping that maybe there is a way to use max(seq) function after insert a record in table-2, and add 1 to the max(seq) to generate the next seq.

ttran

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-10-02 : 19:48:51

You could, but it would mean either dynamic SQL or a cursor. Neither of which are that good for performance. My example is a bulky statement but would probably be the fastest option for execution.
Is this going to be a once-off normalisation or will you want to run this regularly? That will influence what approach you take.

ttran
Starting Member

23 Posts

Posted - 2006-10-03 : 11:16:23

Your code will solve the first input file (table-1). But how can I insert the 2nd input file (table-3) into table-2, which might or might not have some seq for each id?

table-3
id subj1 subj2 subj3 subj4
1 x1 x2 x3 x4
2 x2 x3 x5
4 x1 x3

After insert, the table-2 will have:
id seq subj
1 5 x1
1 6 x2
1 7 x3
1 8 x4
2 3 x2
2 4 x3
2 5 x5
4 1 x1
4 2 x3

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-03 : 11:44:21

Something similar to this?

http://www.sqlteam.com/item.asp?ItemID=2652

Madhivanan

Failing to plan is Planning to fail

ttran
Starting Member

23 Posts

Posted - 2006-10-03 : 14:24:48

Awesome !!!
Thank you, thank you.
I'm new, but I learn a lot from this website.

ttran

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources