adding IDENTITY to existing Column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

adding IDENTITY to existing Column

Author

Topic

kevin_co
Starting Member

5 Posts

Posted - 2009-07-08 : 16:46:05

I currently have a Table named Customers with a column named CustomerNo as the primary key and I want to add the IDENTITY property to the Column. I tried using SSMS but got an error. Then tried using the ALTER TABLE with ALTER COLUMN.

The column is CustomerNo int PRIMARY KEY NOT NULL so I tried:

ALTER TABLE Customers
ALTER COLUMN CustomerNo int IDENTITY PRIMARY KEY NOT NULL

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-08 : 16:53:17

SSMS gave you an error?

What was it

In any case, you have to copy the pk to another column

Drop the pk

Create the column with IDENTITY

SET IDENTITY_INSERT ON

INSERT THE ROWS

SET IDENTITY_INSERT OFF

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-09 : 09:51:30

http://www.mssqltips.com/tip.asp?tip=1397

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources