how to add identity column to an existing table

Posted by Parthibansk under Sql Server on 12/20/2012 | Points: 10 | Views : 1092 | Status : [Member] | Replies : 3
i have column name id in which in inserted 1640 values manually..

now i have requirement from client..column id values should be automatically inserted startinhg from 1641..

is it possible to alter table and insert identity to existing column ID OR

shall i try QUERY BELOW:

DBCC CHECKIDENT('PRICES',RESEED,1640)




Responses

Posted by: Ranjeet_8 on: 12/20/2012 [Member] [MVP] Gold | Points: 25

Up
0
Down
DBCC CHECKIDENT('PRICES',RESEED,1640)
The following line resets the Identity value for the Prices to 1640 so that the next record added starts at 1641.

Parthibansk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Parthibansk on: 12/20/2012 [Member] Starter | Points: 25

Up
0
Down
how to alter table or query to add identity to the column id..

alter table prices alter id int identity(1640,1)

this is not working

Parthibansk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ranjeet_8 on: 12/20/2012 [Member] [MVP] Gold | Points: 25

Up
0
Down
IDENTITY columns contain system-generated values that uniquely identify each row within a table.They are used to store sequential numbers, such as invoice numbers or employee numbers. MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature.
You can Reset the IDENTITY
try this
DBCC CHECKIDENT ('YourTableName', RESEED, 1640)

OR
-- Create Table

CREATE TABLE [dbo].[TestTable](
[ID] [int] IDENTITY(11,1) NOT NULL,
[Emp_Name] [Varchar](50) NULL
) ON [PRIMARY]
GO


Parthibansk, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response