Can you add identity to existing column in sql server 2008?

Posted by bmutch on Stack Overflow See other posts from Stack Overflow or by bmutch
Published on 2010-02-15T17:01:31Z Indexed on 2010/05/31 1:02 UTC
Read the original article Hit count: 307

Filed under:
|

In all my searching I see that you essentially have to copy the existing table to a new table to chance to identity column for pre-2008, does this apply to 2008 also?

thanks.

most concise solution I have found so far:

CREATE TABLE Test 
 ( 
   id int identity(1,1), 
   somecolumn varchar(10) 
 ); 

 INSERT INTO Test VALUES ('Hello'); 
 INSERT INTO Test VALUES ('World'); 


 -- copy the table. use same schema, but no identity 
 CREATE TABLE Test2 
 ( 
   id int NOT NULL, 
   somecolumn varchar(10) 
 ); 

 ALTER TABLE Test SWITCH TO Test2; 

 -- drop the original (now empty) table 
 DROP TABLE Test; 

 -- rename new table to old table's name 
 EXEC sp_rename 'Test2','Test'; 

 -- see same records 
 SELECT * FROM Test; 

© Stack Overflow or respective owner

Related posts about sql-server

Related posts about sql-server-2008