SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Creating a Sequential Record Number field
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 09/20/2000 :  23:01:52  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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.

Anonymous
Starting Member

0 Posts

Posted - 09/27/2000 :  09:58:08  Show Profile  Reply with Quote
Question?

by David Russ, CNE





Why wouldn't one use a table with an identity column.
Do the insert statement, excluding the auto increment indentity column.
Then use the SELECT @LastNumber = @@Identity to get the last, generated
identity number.
With Identity columns, you don't ever have to worry about dupes, or
using temp tables and lots of gyrations to get a sequential number.

For instance, we are a transaction processor. One of the features our
system has is an 'Auto_Award' that is basically a gift cert that is
issued in real time from a credit card terminal. These certs have a
serial number that is generated on the backend via an identity column.

They are sequential, however, sequential across our entire merchant
base, not a single merchant. This is done for security and control.
Anyway, I thought I would ad my .05 cents worth. Also, I hope that I
didn't misunderstand your question or method of using the UPDATE
statement.

Best regards,
David Russ, CNE

Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 09/27/2000 :  19:28:09  Show Profile  Visit graz's Homepage  Reply with Quote
Renumbering

Actually this is meant more to renumber fields. Using IDENTITY and then deleting records will create a hole and this will fill those holes.

Go to Top of Page

rrb
SQLTeam Poet Laureate

Australia
1479 Posts

Posted - 03/21/2002 :  22:28:02  Show Profile  Reply with Quote
graz

thanks for THE coolest SQL I've ever seen.

And now I realise I've been piqued.http://www.sqlteam.com/item.asp?ItemID=2368

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).
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=13436

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

Edited by - rrb on 03/26/2002 17:41:18
Go to Top of Page

aclarke
Posting Yak Master

Canada
133 Posts

Posted - 03/29/2002 :  00:04:21  Show Profile  Visit aclarke's Homepage  Send aclarke an AOL message  Click to see aclarke's MSN Messenger address  Send aclarke a Yahoo! Message  Reply with Quote
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
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 03/29/2002 :  00:45:51  Show Profile  Visit graz's Homepage  Reply with Quote
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.
Go to Top of Page

aclarke
Posting Yak Master

Canada
133 Posts

Posted - 03/29/2002 :  00:52:20  Show Profile  Visit aclarke's Homepage  Send aclarke an AOL message  Click to see aclarke's MSN Messenger address  Send aclarke a Yahoo! Message  Reply with Quote
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.

Go to Top of Page

hexy
Starting Member

New Zealand
1 Posts

Posted - 12/20/2005 :  23:23:56  Show Profile  Reply with Quote
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.
Go to Top of Page

WisTex
Starting Member

2 Posts

Posted - 03/05/2006 :  01:08:53  Show Profile  Visit WisTex's Homepage  Send WisTex an AOL message  Send WisTex a Yahoo! Message  Reply with Quote
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).
Go to Top of Page

WisTex
Starting Member

2 Posts

Posted - 03/05/2006 :  11:31:29  Show Profile  Visit WisTex's Homepage  Send WisTex an AOL message  Send WisTex a Yahoo! Message  Reply with Quote
Never mind. Found out you can do simple math in the UPDATE statement.
Go to Top of Page

yaronkl67
Starting Member

7 Posts

Posted - 10/25/2006 :  21:07:05  Show Profile  Reply with Quote
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.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15659 Posts

Posted - 10/25/2006 :  21:15:10  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

yaronkl67
Starting Member

7 Posts

Posted - 10/25/2006 :  21:22:04  Show Profile  Reply with Quote
Thanks a lot! that works beautifully.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 10/25/2006 :  21:56:42  Show Profile  Reply with Quote
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



Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000