Identity property

Question

Identity property

karunakar2351

Right there with Babe

Points: 729
More actions
July 18, 2013 at 6:04 am

#280333

Hi,
I have a table with some columns like
Table name:Mydata
columns:sid,sname,course
with some data.
so, can i add a Identity property to the sid column after data inserted in to the table?

Viewing 14 posts - 1 through 13 (of 13 total)

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Yes (of course if the data type allows it).

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Koen, are you sure about that? You can add a new column with an identity property, but I can't see any way of adding the identity property to an existing column.

John

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

John Mitchell-245523 (7/18/2013)
Koen, are you sure about that? You can add a new column with an identity property, but I can't see any way of adding the identity property to an existing column.
John

You can't use ALTER TABLE to add the identity property.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 4

You can't do it directly with any ALTER statement that I know of.

But you can do something like this even if it is a bit convoluted:

-- Test data

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL

DROP TABLE #TempTable

IF OBJECT_ID('tempdb..#NewTable') IS NOT NULL

DROP TABLE #NewTable

CREATE TABLE #TempTable

(

[sid] INT NULL,

[sname] NVARCHAR(50) NULL,

[course] INT NULL

)

INSERT INTO #TempTable

SELECT 10,'John',123 UNION ALL

SELECT 222,'David',124 UNION ALL

SELECT 32,'Mary',125 UNION ALL

SELECT 41,'Will',124 UNION ALL

SELECT 54,'Gene',127 UNION ALL

SELECT 689,'Tom',125

SELECT * FROM #TempTable

-- Make a copy of the orignal table with an IDENTITY column

SELECT

IDENTITY(INT,1,1) AS [sid]

,sname

,course

INTO #NewTable

FROM #TempTable

-- Determine which new rows will need to be deleted

DECLARE @MaxNewSid INT

SELECT @MaxNewSid = MAX([sid]) FROM #NewTable

-- Insert the old data into the new table

-- updating the new IDENTITY column with [sid] values

SET IDENTITY_INSERT #NewTable ON

INSERT INTO #NewTable

([sid],sname,course)

SELECT

[sid]

,sname

,course

FROM #TempTable

SET IDENTITY_INSERT #NewTable OFF

-- Delete the rows inserted during table initialization

DELETE FROM #NewTable

WHERE [sid] <= @MaxNewSid

-- Drop and re-create #TempTable with new IDENTITY col

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL

DROP TABLE #TempTable

GO

CREATE TABLE #TempTable

(

[sid] INT IDENTITY(1,1) NOT NULL,

[sname] NVARCHAR(50) NULL,

[course] INT NULL

)

-- Now copy the new data back to the

-- original table name and structure

SET IDENTITY_INSERT #TempTable ON

INSERT INTO #TempTable

([sid],sname,course)

SELECT

[sid]

,sname

,course

FROM #NewTable

SET IDENTITY_INSERT #TempTable OFF

-- Done with the temp #NewTable

DROP TABLE #NewTable

-- Now insert a new row

-- ID will increment by one

INSERT INTO #TempTable

(sname,course)

SELECT 'Alison',123

SELECT * FROM #TempTable

ORDER BY [sid]

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

John Mitchell-245523 (7/18/2013)
Koen, are you sure about that? You can add a new column with an identity property, but I can't see any way of adding the identity property to an existing column.
John

I am sure, I tested it out. I did use the designer though and there it was a piece of cake.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

Ah yes, hadn't thought of that. I guess that behind the scenes it creates a new column and copies the data across - something to bear in mind if you have a large number of rows in the table.

John

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 7

John Mitchell-245523 (7/19/2013)
Ah yes, hadn't thought of that. I guess that behind the scenes it creates a new column and copies the data across - something to bear in mind if you have a large number of rows in the table.
John

I didn't say it was a good idea 😉 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

kapil_kk SSC-Insane Points: 21316 More actions · Answer 8

NO you cant add the IDENTITY property to the existing column using a sql query....

But it can be done from edit design of a table

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 9

kapil_kk (7/19/2013)
NO you cant add the IDENTITY property to the existing column using a sql query....
But it can be done from edit design of a table

Of course you can. How do you think the designer makes the DDL change?

In the designer, you can "Generate the change script".

Steve

kapil_kk SSC-Insane Points: 21316 More actions · Answer 10

Steve-3_5_7_9 (7/19/2013)
kapil_kk (7/19/2013)
NO you cant add the IDENTITY property to the existing column using a sql query....
But it can be done from edit design of a table
Of course you can. How do you think the designer makes the DDL change?
In the designer, you can "Generate the change script".
Steve

When we right click on the table and click on design...from this we can set the identity property to an existing column...

While using ALTER command I never assigned identity property to an existing...

If you know then please tell us I am already to learn new things

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

Steve-3_5_7_9 (7/19/2013)
kapil_kk (7/19/2013)
NO you cant add the IDENTITY property to the existing column using a sql query....
But it can be done from edit design of a table
Of course you can. How do you think the designer makes the DDL change?
In the designer, you can "Generate the change script".
Steve

Semantics. You can't do it with a single statement ("a sql query"), but you can with a script/batch.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

kapil_kk SSC-Insane Points: 21316 More actions · Answer 12

ChrisM@Work (7/19/2013)
Steve-3_5_7_9 (7/19/2013)
kapil_kk (7/19/2013)
NO you cant add the IDENTITY property to the existing column using a sql query....
But it can be done from edit design of a table
Of course you can. How do you think the designer makes the DDL change?
In the designer, you can "Generate the change script".
Steve
Semantics. You can't do it with a single statement ("a sql query"), but you can with a script/batch.

Chris can you please tell me the way with a script/batch...

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 13

kapil_kk (7/19/2013)
Chris can you please tell me the way with a script/batch...

Change it in the designer. Before saving it, hit the "Generate change script" button.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP