How to add Identity an existing column

Question

How to add Identity an existing column

PRAMANA.DBA

SSCertifiable

Points: 5507
More actions
April 3, 2012 at 3:51 am

#74249

Hi All,
Can any one help to me ,
How to add Identity an existing column, It is empty table.
CREATE table Temp(ID int, Name varchar(10))
SELECT 1 as ADTID, ID,Name INTO temp1 from temp
ALTER TABLE temp1 ALTER COLUMN ADTID INT NOT NULL
ALTER TABLE temp1 ADD CONSTRAINT PKKK PRIMARY KEY (ADTID)
I need to add identity on ADTID column, How i can Add
Thanks
Ramana.P

Viewing 6 posts - 1 through 5 (of 5 total)

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

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 1

You can't alter a column and add IDENTITY, You have either create a new table or drop the column and re add with the IDENTITY property

Andy

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

Modified your code, in an insiginificant manner to:

CREATE table Temp(ID int, Name varchar(10))

CREATE table Temp1(ADTID INT,ID INT,Name varchar(10))

INSERT INTO Temp -- Add 1 row of data to Temp

SELECT 10,'Some name'

SELECT 1 as ADTID, ID,Name INTO temp1 from temp

SELECT * FROM temp1 -- to verify data was added

/*Result:

ADTIDIDName

110Some name

Then executed your code */

ALTER TABLE temp1 ALTER COLUMN ADTID INT NOT NULL

ALTER TABLE temp1 ADD CONSTRAINT PKKK PRIMARY KEY (ADTID)

Using SSMS - Design table

Opened table Temp1

Selected ADTID column, in the properties window SELECTED IDENTITY and selected "Yes", with increment and seed both set to 1.

Saved the result .. Then to verify results ran the following

INSERT INTO temp1

SELECT 100,'NO NAME'

SELECT ADTID,ID,NAME FROM temp1

Result:

ADTIDIDNAME

110Some name

2100NO NAME

Try it it might also work for you.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

PRAMANA.DBA SSCertifiable Points: 5507 More actions · Answer 3

HI..

I need through script.. Not through design , I have to keep that script in procedure because, i am creating tables Dynamically by using sp.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 4

Your Table:

CREATE table Temp(ID int, Name varchar(10))

--Inserting data for testing

INSERT INTO Temp

SELECT 1,'First' UNION ALL

SELECT 2,'Second' UNION ALL

SELECT 3,'Third'

The new table you need to create using your SP

CREATE TABLE [dbo].[Temp1](

[ADTID] [int] IDENTITY(1,1) NOT NULL,

[ID] [int] NULL,

[Name] [varchar](10) NULL

) ON [PRIMARY]

INSERT INTO Temp1(ID,Name)

SELECT ID,Name FROM Temp

SELECT ADTID,ID,Name FROM Temp1

My results:

ADTID ID Name

1 1 First

2 2 Second

3 3 Third

Hope this time I understood you correctly.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Prassad Dabbada V R Ten Centuries Points: 1251 More actions · Answer 5

you can also check the below article from Pinal Dave

http://blog.sqlauthority.com/2009/05/03/sql-server-add-or-remove-identity-property-on-column/