Sequential ID Field in SQL 2005 Table

I need to update my SQL 2005 table to automatically add the next ID number when I add new data to the field. I have attached an UPDATE query of my existing table and was hoping some type of update would make my sequential numbering would work.  
UPDATE [TrackVent].[dbo].[Humidifier_Base]
   SET [ID] = <ID, int,>
      ,[SERIAL #] = <SERIAL #, nvarchar(255),>
      ,[P/N OR SVC] = <P/N OR SVC, nvarchar(255),>
      ,[MODEL] = <MODEL, nvarchar(255),>
      ,[CUSTOMER] = <CUSTOMER, nvarchar(255),>
      ,[ID #] = <ID #, nvarchar(255),>
      ,[END CUSTOMER] = <END CUSTOMER, nvarchar(255),>
      ,[INVOICE] = <INVOICE, nvarchar(255),>
      ,[INV DATE] = <INV DATE, datetime,>
      ,[ACTION] = <ACTION, nvarchar(255),>
      ,[TYPE] = <TYPE, nvarchar(255),>
      ,[RGA] = <RGA, nvarchar(255),>
      ,[COMMENTS 1] = <COMMENTS 1, nvarchar(255),>
      ,[COMMENTS 2] = <COMMENTS 2, nvarchar(255),>
 WHERE <Search Conditions,,>

Open in new window

allenkentAsked:
Who is Participating?
 
mimran18Connect With a Mentor Commented:
Hi,
   You need to make the column as an identity column to do an auto increment.

Here is the code for it.

Alter Table [table Name] add [Column Name] int identity(1,1)
0
 
allenkentAuthor Commented:
I have 10,000 lines of data already with the ID field already populated.

I run this: (see my fields in code)

ALTER Table TrackVent add ID int identify(1,1)

This is all? This will update my ID field? It looks like it want to add a new column.
0
 
mimran18Commented:
In this case we should not do alter table.

Do you the maximum ID +1 when you need to add a new record or at the time of update you need to do it ?

0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
allenkentAuthor Commented:
I need to add the new number at time of update.

I just tested and the way you gave me worked for creating a new column. I wish I could just update my existing table using design and make it like the ID1 column I just created.
0
 
mimran18Commented:
Ya, You can do it but you need to check no null values should be there in that column (ID).
0
 
allenkentAuthor Commented:
I have no NULL values. All numbers are sequential.
0
 
allenkentAuthor Commented:
I tried:
ALTER Table [tablename] UPDATE ID int indentity(1,1)

AND IT CRASHED MY SYSTEM :)
0
 
mimran18Commented:
No you should not do it like this.
Open it in design view and make it identity.
0
 
allenkentAuthor Commented:
Great solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.