Best way to populate a numeric ID key? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2008 Forums

Transact-SQL (2008)

Best way to populate a numeric ID key?

Author

Topic

malachi151
Posting Yak Master

152 Posts

Posted - 2011-08-02 : 10:35:10

I've got a situation where we need to populate an id column with an integer value. This isn't the primary key on the table and is used for intermediate purposes as part of some other process.

This value get's reset several times and new records get added in and the values get appended to, etc, and we have to be able to seed the value at a certain number.

We're currently using a cursor to loop through and set the values, along the line of WHILE @@FETCH_STATUS = 0 BEGIN SET ID = @key; @key = @key + 1 END, type of thing.

This takes a long time of course. I thought about using ROW_NUMBER() to generate the values but you can't set a seed value for that as far as I know so it would always start at 1.

Is there any other way to do this without using identity insert?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-08-02 : 10:38:28

How about Row_Number()+@seed ??

Corey

I Has Returned!!

malachi151
Posting Yak Master

152 Posts

Posted - 2011-08-02 : 10:40:19

quote:
Originally posted by Seventhnight

How about Row_Number()+@seed ??

Corey

I Has Returned!!

Doh, so obvious, now why didn't I think of that?

Thanks, I'll see if there are any other suggestions as well, but this is a good one...

malachi151
Posting Yak Master

152 Posts

Posted - 2011-08-02 : 13:35:40

Well actually this isn't going to work either. I now realize that there is no unique key on the data starting out, so I can't use ROW_NUMBER because doing so would require a unique key to join the the table on to do the update, unless I use ROW_NUMBER at the time of the insert I supposed, but that will require reloading all the data...

Any other thoughts?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-02 : 13:59:02

why do you need a join for update? cant you do something like


UPDATE t
SET IDCol=UnqiueID
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) + @Seed AS UniqueID
FROM table
)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

malachi151
Posting Yak Master

152 Posts

Posted - 2011-08-02 : 14:51:02

quote:
Originally posted by visakh16

why do you need a join for update? cant you do something like

UPDATE t
SET IDCol=UnqiueID
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) + @Seed AS UniqueID
FROM table
)t

No, this doesn't work. You can't update a sub-query. When I try to run this (a modification of it) I get "Invalid column name 'UnqiueID'."

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-08-02 : 16:59:43

Would using an Identity column work for you or do you need to change the seed between "loos?"

If you only need to set the seed at the start of a batch you might be able to use an Identity column reseeding using DBCC:

DBCC CHECKIDENT 
( 
        table_name
        [ , { NORESEED | { RESEED [ , new_reseed_value ] } } ]
)
[ WITH NO_INFOMSGS ]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-08-02 : 17:19:17

quote:
Originally posted by malachi151

You can't update a sub-query.

Yes you can.

N 56°04'39.26"
E 12°55'05.63"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-03 : 00:41:57

quote:
Originally posted by malachi151

quote:
Originally posted by visakh16

why do you need a join for update? cant you do something like

UPDATE t
SET IDCol=UnqiueID
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) + @Seed AS UniqueID
FROM table
)t

No, this doesn't work. You can't update a sub-query. When I try to run this (a modification of it) I get "Invalid column name 'UnqiueID'."

who told? did you try at least?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

malachi151
Posting Yak Master

152 Posts

Posted - 2011-08-03 : 17:31:42

quote:
Originally posted by visakh16

quote:
Originally posted by malachi151

quote:
Originally posted by visakh16

why do you need a join for update? cant you do something like

UPDATE t
SET IDCol=UnqiueID
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) + @Seed AS UniqueID
FROM table
)t

No, this doesn't work. You can't update a sub-query. When I try to run this (a modification of it) I get "Invalid column name 'UnqiueID'."

who told? did you try at least?

Yeah, when I run the query I get the error noted in the prior post, Invalid column name. I modified the query of course to match the tables I'm using, etc.

The query I ran was as follows:


UPDATE t
SET t.transaction_id = t.UnqiueID
FROM
(
SELECT transaction_id, ROW_NUMBER() OVER (ORDER BY (customer_id, state_code, transaction_date)) + @Seed AS UniqueID
FROM std_Claim_Transactions
)t

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-08-03 : 17:35:09

You spelled unique wrong

t.UnqiueID should be t.UnIQueID

Jim

Everyday I learn something that somebody else already knew

malachi151
Posting Yak Master

152 Posts

Posted - 2011-08-04 : 15:02:44

Doh, that's what I get for cutting and pasting :p

Thanks guys

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources