asked on

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

ASKER CERTIFIED SOLUTION

mimran18

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

allenkent

ASKER

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.

mimran18

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 ?

allenkent

ASKER

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.

mimran18

Ya, You can do it but you need to check no null values should be there in that column (ID).

allenkent

ASKER

I have no NULL values. All numbers are sequential.

allenkent

ASKER

I tried:
ALTER Table [tablename] UPDATE ID int indentity(1,1)

AND IT CRASHED MY SYSTEM :)

mimran18

No you should not do it like this.
Open it in design view and make it identity.

allenkent

ASKER

Great solution.