| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
|
|
Anonymous
Starting Member
0 Posts |
 Posted - 09/27/2000 : 09:58:08
|
| 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
|
 |
|
|
graz
Chief SQLTeam Crack Dealer
USA
4128 Posts |
|
|
rrb
SQLTeam Poet Laureate
Australia
1478 Posts |
|
|
aclarke
Posting Yak Master
Canada
133 Posts |
Posted - 03/29/2002 : 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
USA
4128 Posts |
Posted - 03/29/2002 : 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
Canada
133 Posts |
Posted - 03/29/2002 : 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
New Zealand
1 Posts |
Posted - 12/20/2005 : 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 - 03/05/2006 : 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 - 03/05/2006 : 11:31:29
|
| Never mind. Found out you can do simple math in the UPDATE statement. |
|
|
|
yaronkl67
Starting Member
7 Posts |
Posted - 10/25/2006 : 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
USA
15559 Posts |
Posted - 10/25/2006 : 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 - 10/25/2006 : 21:22:04
|
| Thanks a lot! that works beautifully. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
| |
Topic |
|
Article: Creating a Sequential Record Number field
