How to add IDENTITY to an existing table. 1

[ibwebn65]

Does anyone know how to write script to not only make a pre-existing and in use table column a primary key but also set the identity values?

I have inherited a database. It has a table being used, which is already populated with data. The table has an ID (int)(Not Null) field being used as a primary key, but it is NOT marked in SQL as a primary key. I have been able to write the following script to convert the ID table to a primary key field:

ALTER TABLE [dbo].[MYTABLE] WITH NOCHECK
ADD CONSTRAINT [PK_MYTABLE] PRIMARY KEY CLUSTERED
([ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

My main problem is I have NOT been able to figure out how to write the script to set the identity to YES with the identity seed set to 1 and the identity increment set to 1.

If it helps, the identity seed does not have to be 1. It can be set to anything. Also, if it helps, the identity information can be set before or with the primary key script.

 

[NoCoolHandle]

Rule #1. You can't modify a column to add the indentity property to it.

(I tried very hard a few years ago, even hackin the system tables to achieve it, and had very poor success. I found you could take it away, but not add it..)

sooooo..

you need to add a column and the identity property at the same time..

e.g.
create table a (c1 int)
go
alter table a
add c3 int identity primary key
go
insert into a values(2)
go
select * from a

 

[ibwebn65]

Thanks NoCoolHandle. That helps. I was able to create a new column with the primary key and identity properties. Then I was able to add the data from the orginal ID column by surrounding the insert code with the IDENTITY_INSERT command.

Example:
========
set IDENTITY_INSERT MYTABLE ON
-- code to insert data
set IDENTITY_INSERT MYTABLE OFF