?
Solved

Sequential ID Field in SQL 2005 Table

Posted on 2011-10-07
9
Medium Priority
?
292 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:allenkent
  • 5
  • 4
9 Comments
 
LVL 9

Accepted Solution

by:
mimran18 earned 2000 total points
ID: 36934263
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
 

Author Comment

by:allenkent
ID: 36934277
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
 
LVL 9

Expert Comment

by:mimran18
ID: 36934288
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:allenkent
ID: 36934303
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
 
LVL 9

Expert Comment

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

Author Comment

by:allenkent
ID: 36934340
I have no NULL values. All numbers are sequential.
0
 

Author Comment

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

AND IT CRASHED MY SYSTEM :)
0
 
LVL 9

Expert Comment

by:mimran18
ID: 36934361
No you should not do it like this.
Open it in design view and make it identity.
0
 

Author Closing Comment

by:allenkent
ID: 36950760
Great solution.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question