none
How to alter an existing table column as identity without dropping the table?

    Question

  • Hi all,

             I have created a table as below mentioned. Then I want to alter the ID column as identity(1,1) without dropping the table as well as losing the data.

    create table dbo.IdentityTest
    (
    id int not null,
    descript varchar(255) null,
    T_date datetime not null
    )

    Can anyone please assist me to do such a task?

            Thanks in advance!

    Wednesday, November 20, 2013 6:51 AM

Answers

All replies

  • I assume, you do not have any constraint and index on ID column of the table.

    Try the below:

    create table dbo.IdentityTest
    (
    id int not null,
    descript varchar(255) null,
    T_date datetime not null
    )
    
    Insert into IdentityTest Select 1,'test',GETDATE()
    Insert into IdentityTest Select 10,'test',GETDATE()
    
    ALTER TABLE dbo.IdentityTest Add Id1 int Identity(1,1)
    
    ALTER TABLE dbo.IdentityTest DROP COLUMN id
    
    EXEC sp_rename 'IdentityTest.Id1' ,'id','COLUMN'
    
    Select * From dbo.IdentityTest
    
    Drop table identityTest


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by Uri DimantMVP, Editor Wednesday, November 20, 2013 7:37 AM
    • Unproposed as answer by SathyJay Thursday, November 21, 2013 10:58 AM
    Wednesday, November 20, 2013 6:59 AM
  • Also , few more alternatives explained here http://blog.sqlauthority.com/2009/05/03/sql-server-add-or-remove-identity-property-on-column/

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, November 20, 2013 7:05 AM
  • Hi SathyJay,

    if you want to keep the ID's that are already in the table, you can use the following solution:

    create table dbo.IdentityTest (
    	id int not null,
    	descript varchar(255) null,
    	T_date datetime not null
    )
    
    Insert into IdentityTest Select 1,'test',GETDATE()
    Insert into IdentityTest Select 10,'test',GETDATE()
    
    CREATE TABLE dbo.IdentityTest_TEMP (
    	id int Identity(1,1) not null,
    	descript varchar(255) null,
    	T_date datetime not null
    )
    
    SET IDENTITY_INSERT dbo.IdentityTest_TEMP ON
    
    INSERT INTO dbo.IdentityTest_TEMP(id, descript, T_date)
    SELECT * FROM dbo.IdentityTest
    
    SET IDENTITY_INSERT dbo.IdentityTest_TEMP OFF
    
    DROP TABLE dbo.IdentityTest
    EXEC sp_rename 'IdentityTest_TEMP' ,'IdentityTest'
    
    INSERT INTO dbo.IdentityTest(descript, T_date) VALUES('Test', getdate())
    
    Select * From dbo.IdentityTest
    
    DROP TABLE IdentityTest

    Regards

    Alexander

    Wednesday, November 20, 2013 2:40 PM
  • I'm asking without dropping the table and column also. 

    Would it be possible?

    Thanks!

    Thursday, November 21, 2013 10:59 AM
  • I'm asking without dropping the table and column also. 

    Would it be possible?

    Thanks!

    Without dropping table and column(without any change in structure), there is no possible way.
    Thursday, November 21, 2013 11:07 AM
  • Thanks for your valuable reply!!!
    Thursday, November 21, 2013 11:23 AM