How to generate sequence in SQL Server

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

SQL Server Development (2000)

How to generate sequence in SQL Server

Author

Topic

rkumar28
Starting Member

49 Posts

Posted - 2005-10-14 : 17:44:02

Hi,
I am trying to load data into table A from table B.
Table A has three columns(a1,a2,a3) and Table B has 5 columns(b1,b2,b3,b4,b5).
In table A, a1 and a3 are unique primary keys. Is there a way to generate the sequence number for column a3 during the insert below.

I am trying to do:

insert into table A(a1,a2,a3)
select
b1,b2,(need a sequence number here for a3)
from
table B

Thanks for your help in this regard.

Raj

Kristen
Test

22859 Posts

Posted - 2005-10-15 : 01:50:45

Insert from TableA into a temporary table first, the temporary table can have an Identity column for "a3", and then insert from the temporary table to TableB

(You could just put the unique PK columns in the temporary table and JOIN it to TableA during the Insert into TableB)

Kristen

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-10-15 : 16:44:33

a1 and a3 cannot "each" be unique primary keys. They can both be unique, of course (why you would want this, I'm not sure...), but a table can have only one "primary key".

Now, if you mean that a1 and a3 TOGETHER represent a unique primary key, with a3 as an incrementing ordinal count of a1 records, then you can do this without a temporary table:

insert into table A(a1,a2,a3)
select	B.b1 as a1,
	B.b2 as a2,
	count(*) as a3
from	B
	inner join B C
		on B.b1 = c.b1
		and B.b2 >= C.b2
group by B.b1,
	B.b2

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-10-15 : 17:20:16

Hi blindman!
Paying a visit?

rockmoose

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-10-15 : 19:02:32

Trying it out. Two things I find annoying; the division of SQL Server questions into different topic sections (I don't want to have to search every sub-forum) and the fact that the interface does not indicate which threads I have already posted to (as dbforums does).

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-10-15 : 23:11:43

Try the Active Topics link:

http://www.sqlteam.com/forums/active.asp

It accomplishes both of the things you're looking for.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-10-16 : 01:47:10

Thank you! That looks much better!

Kristen
Test

22859 Posts

Posted - 2005-10-16 : 01:50:03

"the interface does not indicate which threads I have already posted to"

Subject should show in Bold if you have contributed - but not from all the possible lists, so "Active topics" does, "Search" doesn't.

There is a list of recent posts on your membership details.

At the top left of the Active Topics page is a half-purple half-yellow folder; click that to refresh the Active Topics list - i.e. show anything newer than the most recent entry in the list you are looking at - I work down the list, then click that "Mark all topics as read" button.

Kristen

Kristen
Test

22859 Posts

Posted - 2005-10-16 : 01:51:32

"the division of SQL Server questions into different topic sections"

I don't read Access, Report Server and DTS forums 'coz I don't know anything about them ... so I think that's the point of the division, but maybe I've misunderstood your point?

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-10-16 : 06:36:45

quote:
Originally posted by blindman

Trying it out. Two things I find annoying; the division of SQL Server questions into different topic sections (I don't want to have to search every sub-forum) and the fact that the interface does not indicate which threads I have already posted to (as dbforums does).

Welcome aboard, nice to have you here

!

Since the vast majority of questions are SQL Server related,
I find the subdivisions pretty nice.
But I understand, it's a fine line between too much / too little subdividing.

rkumar28
Starting Member

49 Posts

Posted - 2005-10-25 : 15:01:17

quote:
Originally posted by blindman

a1 and a3 cannot "each" be unique primary keys. They can both be unique, of course (why you would want this, I'm not sure...), but a table can have only one "primary key".
Now, if you mean that a1 and a3 TOGETHER represent a unique primary key, with a3 as an incrementing ordinal count of a1 records, then you can do this without a temporary table:
insert into table A(a1,a2,a3)
select B.b1 as a1,
B.b2 as a2,
count(*) as a3
from B
inner join B C
on B.b1 = c.b1
and B.b2 >= C.b2
group by B.b1,
B.b2

Hi Blindman and Kristen,
Thanks you very much for the suggestion on this. I tried the suggested fix. It worked.
But have another problem:
Actually I wanted to increment the values of a3 as an incrementing ordinal count of a1 records which works fine. But the a3 resets itself for new a1 records.

But if the new records come in into table 'A' and in the new records there are some values of a1 that are already there in the a1 of table 'A' ...I need to increment a3 from the Max value of the a3 instead of starting from 1.

For example:

Table 'A' already has the record below:

a1 a3
1234 1
1234 2
1234 3
1234 4
5678 1
5678 2
5678 3

Now new records comes in thats need to be loaded into Table 'A'

a1 a3
789 1
789 2
1234 1 --> This needs to start from 5 and not 1.The value should start from max of old value(already in Table 'A' ) + 1
1234 2 ----> This needs to be 6 and not 2
5678 1 ----> Similary this needs to be 4 and not 1
5678 2 ----> This needs to be 5 and not 2.

After load the table 'A' should look something like this:

a1 a3
1234 1
1234 2
1234 3
1234 4
5678 1
5678 2
5678 3
789 1
789 2
1234 1 --> This needs to start from 5 and not 1.The value should start from max of old value(already in Table 'A' ) plus 1
1234 2 --> This needs to be 6 and not 2
5678 1 --> Similary this needs to be 4 and not 1
5678 2 --> This needs to be 5 and not 2.

I will appreciate any help and advice on this.....

Thanks

Raj

rkumar28
Starting Member

49 Posts

Posted - 2005-10-25 : 16:15:05

Any advice anyone.... on how to acheive this. Will appreciate the help....

Thanks

Raj

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-10-25 : 16:43:18

The solutions Kristen and I provided were suitable for one-time population of a dataset. They are not appropriate for a dataset involved in constant transactional processing (new data being added frequently). In such an environment, all your inserts should be handled through a stored procedure that incorporates the business logic you require for increnting your ID values. It is possible to do this using a trigger on the table, but I am hesitant to recommend this as a solution in your case.
In the end, you really need to consider whether these ordinal values need (or should) be permanently assigned to the data and thus must be stored in the table. For instance, do you need the IDs recalculated in records are deleted, or can you deal with gaps in the sequence? If possible, these ordinal values should be created dynamically whenever required, rather than stored in the table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources