Article: Creating a Sequential Record Number field

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

Site Related Forums

Article Discussion

Article: Creating a Sequential Record Number field

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2000-09-20 : 23:01:52

Ok, this has to be one of the coolest things I've discoverd about SQL Server in quite a while. It is a way to create a sequential record number field on a table using a single update statement. Until I discovered this, I would have said this was impossible. Read on for the solution.

Article Link.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-21 : 22:28:02

graz

thanks for THE coolest SQL I've ever seen.

And now I realise I've been piqued.

[url]http://www.sqlteam.com/item.asp?ItemID=2368[/url]

Anyhow, here's two variations.

1. This one was to concatenate together a "where" clause based on every column in a table (or in this case, every nvarchar field).
[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=13436[/url]

2. This one extends this process to create a concatenated list, grouped by another field in the table.
[url]http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=14095[/url]

Edited by - rrb on 03/26/2002 17:41:18

aclarke
Posting Yak Master

133 Posts

Posted - 2002-03-29 : 00:04:21

This works great on an update, but how about an insert? I tried to do something like this:

quote:

declare @intCounter int
select @intCounter = coalesce((max(shippingZoneID) + 1), 1) from ShippingZone where shippingZoneID is not NULL

select identity(int, @intCounter, 1) as shippingZoneID, destinationZipCode, shippingZone, shippingMethodID
into #temp
from temp_ShippingZone

but it seems like SQL Server doesn't like you definiting identity fields with variables.

After much hemming and hawing, I used Graz's most EXCELLENT tip to just add the record numbering afterwards. I wish there was a way to skip this last step though, although it only adds a couple seconds to the processing time of my script.

Here's what I had in the end, in case anybody's interested in an application of this:

quote:

declare @intCounter int
select @intCounter = coalesce(max(shippingZoneID), 1) from ShippingZone where shippingZoneID is not NULL
declare @when datetime
set @when = getDate()

insert into ShippingZone (
destinationZipCode, shippingZone, shippingMethodID,
time_validFrom, time_validTo, time_transactionStart, time_transactionEnd, modifiedBy
)

select
destinationZipCode, shippingZone, shippingMethodID,
@when, '9999-12-31', @when, '9999-12-31', 5
from temp_ShippingZone

update ShippingZone
set @intCounter = shippingZoneID = @intCounter + 1
where shippingZoneID is NULL

Edited by - aclarke on 03/29/2002 00:05:09

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-03-29 : 00:45:51

Why don't you create a temp table with an identity column and then insert into it?

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

aclarke
Posting Yak Master

133 Posts

Posted - 2002-03-29 : 00:52:20

quote:

Why don't you create a temp table with an identity column and then insert into it?

This didn't work because I don't know where to start the identity column. It can't numerically intersect with any values currently in ShippingZone.shippingZoneID. So I wanted to start with max(shippingZoneID) + (plus) 1 which is what I was trying to do with identity(int, @intCounter, 1).

Yes, this would have worked fine if I knew with which number I could start my identity field.

hexy
Starting Member

1 Post

Posted - 2005-12-20 : 23:23:56

quote:
The only drawback is that it will determine the order based on the physical order of the table.

I had to create a script to make sure that the order of my image gallery could be reset/standardised - so here is the work-around to apply a sequential number to a column in the order you desire.

DECLARE @index int
SET @index = -1

UPDATE gallery SET @index = ordinal = @index + 1 
WHERE imageId IN (SELECT TOP 1000 imageId FROM gallery WHERE galleryId = 207 ORDER BY ordinal)

NB: The "TOP 1000" is required when specifying an "ORDER BY" clause in a sub-select.

WisTex
Starting Member

2 Posts

Posted - 2006-03-05 : 01:08:53

I wonder if a similar technique can be used to increment a single record using a single SQL statement. Is there a way to do that? Right now the only way I know how is to do a SELECT, find out the value and then do an UPDATE if the record exists, otherwise do an INSERT. It'd be nice to do all that in one statement. It would also prevent missing updates (say if user1 is updating at the same exact time as user2, you could possibly wind up where one of the user's updates are overwritten by the other user's updates... example if they hit at the same time: USER1 SELECT value=100, USER2 SELECT value=100, USER1 UPDATE value=100+1, USER2 UPDATE value=100+1).

WisTex
Starting Member

2 Posts

Posted - 2006-03-05 : 11:31:29

Never mind. Found out you can do simple math in the UPDATE statement.

yaronkl67
Starting Member

7 Posts

Posted - 2006-10-25 : 21:07:05

This was very helpfull. Didn't know about this syntax.
I have a problem that is slightly more complicated: I need to assign numbers to groups in a table.
For example:
create table #tmp
(
GROUP_ID int,
LINE_NO int,
TXT varchar(10)
)

insert #tmp
(GROUP_ID,TXT)
select 1,'this'
union all select 1,'is'
union all select 2,'an'
union all select 2,'example'

declare @intCounter int
set @intCounter = 0
update #tmp
SET @intCounter = LINE_NO = @intCounter + 1

select * from #tmp
drop table #tmp

The table looks as follows:

GROUP_ID LINE_NO TXT
----------- ----------- ----------
1 1 this
1 2 is
2 3 an
2 4 example

But I want it to look as:
GROUP_ID LINE_NO TXT
----------- ----------- ----------
1 1 this
1 2 is
2 1 an
2 2 example

The only solution I can think of is using a cursor to iterate through the groups.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-10-25 : 21:15:10

Nope, no cursor needed:

declare @intCounter int, @grp int
set @intCounter = 0
update #tmp
SET @intCounter = LINE_NO = case when @grp=Group_ID THEN @intCounter + 1 ELSE 1 END, @grp=Group_ID

select * from #tmp
drop table #tmp

This only works correctly if the table is clustered on Group_ID. Without a clustered index you can't guarantee that the values increment properly.

yaronkl67
Starting Member

7 Posts

Posted - 2006-10-25 : 21:22:04

Thanks a lot! that works beautifully.

X002548
Not Just a Number

15586 Posts

Posted - 2006-10-25 : 21:56:42

and what value is it anyway...oye surrogate keys

Does anyone rember noramlization

And sequencing rows....the order of data in a rdbms has no meaning

oye

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources