Generate a unique Id..? 3

[kenjoswe]

Hi all,

I have an old table with 30.000 records in which I want to add a numeric Id-field (1,2,3,4,5,6.....)

How can I create a update query that puts in a counter in ID field?

/Kent J.

 

[tlbroadbent]

There is really no way to alter the table and run an update query that sets the values of identity column. You can add the identity column with Enterprise Manager.

Open the table in design mode with Enterprise Manager. Add the column (type int, identity) and save the table. EM will create a new table, with the identity column, insert the old data into the new table, delete the old table and rename the new table.

You could write your own T-SQL code to do the same process as EM - create table, insert recs, drop table, rename table. But why bother?

You should recompile all views and stored procedures that reference the table.
Terry

http://members.home.net/tlbroadbent/index.htm

 

[jnicho02]

There is a cunning trick in a FAQ by MalcolmW that did something like this.

Check out FAQ 5a. My home ---->

http://www.firstlightassociates.co.uk/

visit me for Java and Data Warehousing resources

 

[kenjoswe]

tlbroadbent,

What I had in mind was to update a integerfield with 1,2,3 up to ..30000 and after that change the field to an Identity-field.

Write my own SQL-code was what I wanted.
I should be able to write something like in T-SQL:

cnt = cnt+1


/Kent J.

 

[DougP]

this is a crude workaround, If you have Microsoft Access.
In SQL make add a new field to accept the new number.
In Access create a new database and Link the SQL table to through ODBC.
Create an exact copy of the table sturcture with NO records.
So you should have 2 TABLES. One is linked to SQL the other is not the SQL table has record the other does not yet.
Open the table without record in design view and change the field type to "autonumber". Create an append query to append the records from the SQL table to the new Access table.
In doing so each record will get a unique number.
Create an update query looking for a unique field in SQL to add the new autonumber back.


DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[tlbroadbent]

Thanks, jnicho02. The FAQ is good information. The recommended T-SQL code works!

Also, a column can be changed to identity (SQL 7 and higher) after it is properly filled. That was news to me. I had not found anything that indicated we could do that and every post to a newsgroup that I read said it was not allowed. It's great to learn something new that is so useful. Terry

http://members.home.net/tlbroadbent/index.htm