VB and VBA Users Source Code: Creating a numeric sequential field on an existing table in SQL Server
[
Home
|
Contents
|
Search
|
Reply
| Previous | Next ]
VB/VBA Source Code
Creating a numeric sequential field on an existing table in SQL Server
By:
Andrew Baker
Email (spam proof):
Email the originator of this post
Date:
Friday, July 30, 2004
Hits:
1759
Category:
Database/SQL/ADO
Article:
If you want to create a sequential record number you could create a cursor and move through the records, setting the values one at a time to counter value that you incrememented. This is slow and not a set-oriented operation. Alternatively you can do the following: declare @firstValue int set @firstValue = 0 update myTable SET @firstValue = myColumnId = @firstValue + 1 After running this all the values myColumnId will hold incremented values. Another option is to create a temp table with an identity column and a column for the primary key of the table you wanted to add the sequence number to. Insert all the records into the temp table and the temp table identity column becomes the sequential record counter. You can join back to the original table and update the sequential record number. The only reason you'd still choose this option is if you want a custom sort order. This above trick won't give you that.
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
