IDENTITY property problem

Question

Post reply

IDENTITY property problem

cdun2

SSCertifiable

Points: 7893
More actions
August 6, 2003 at 3:55 pm

#82201

Hello,
I have a column called 'WorkOrderID' in a table called 'tblWOWorkOrder' that is currently set up as follows:
[WorkOrderID] [int] NOT NULL
PRIMARY KEY
The numeric data in the column is in sequential order, and I need to change the column to use the IDENTITY property with a seed beginning at 11443 with an increment of 1. The current MAX value of this column is 11442.
Call me crazy, but when I run the following code to update this column, I get a syntax error near 'IDENTITY':
ALTER TABLE tblWOWorkOrder
ALTER COLUMN WorkOrderID INT IDENTITY(11443,1)NOT NULL
It seems like this should work. I've tried it with the Primary Key constraint still on, and with the constraint dropped. What am I forgetting?
Thanks!
CSDunn

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 1

See http://www.sqlservercentral.com/columnists/sjones/identityreset.asp

cdun2 SSCertifiable Points: 7893 More actions · Answer 2

Thanks for the link to the article. One of the problems in this case is that the column, ‘WorkOrderID’ is not already and IDENTITY column, so using DBCC CHECKIDENT will not help in this situation.

This application is not in production yet, so I wound up truncating the table, dropping the ‘WorkOrderID’ column and adding it back as INT IDENTITY (1,1) NOT NULL, and checked ‘Enable Identity Insert’ for the Transform Data task to ‘tblWOWorkOrder’ in the DTS package. After checking the data and testing with an inserted record, it looks like everything is okay now.

I would still like to know how a column of INT data type that is not already set up as an IDENTITY column, and has data in it, can be altered to be an IDENTITY column set to a specific seed value.

If you have any input on this, please let me know.

Thanks again!

CSDunn

gljjr SSChampion Points: 10080 More actions · Answer 3

After a table has data in it you can't set the field to have the identity property. So in order to do this you will need to create a new table and put the existing data into it. You will need to do something like the following...

CREATE TABLE Foo

WorkOrderID int IDENTITY(1,1)

, sText varchar(255)

SET IDENTITY_INSERT Foo ON

INSERT INTO Foo(WordOrderID, sText)

SELECT WorkOrderID, sText FROM ExistingTable

SET IDENTITY_INSERT Foo OFF

DROP TABLE ExistingTable

EXEC sp_rename 'Foo', 'ExistingTable'

Gary Johnson

Microsoft Natural Language Group

DBA, Sr. DB Engineer

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.