none
Add Identity Column to an Existing Table - SQL server 2008

    Question

  • I have a table T_ACTIVITY_ERRORS which has fields ACTIVITY_NUMBER,ERROR_ID, ERROR_COMMENTS etc and the number of records in the table are close to 20,000. I want to add a new column ACTIVITY_ERROR_ID ( identity field) to the table. Please let me know how can i accomplish this.
    Thursday, October 25, 2012 1:57 PM

Answers

  • Hi Ram,

    Please refer this example,

    CREATE TABLE ManishK (Data INT)
    Insert into ManishK values(1),(2)
    SELECT * FROM ManishK order by data
    
    ALTER TABLE	ManishK ADD RowID INT IDENTITY(1,1)


    Thanks
    Manish

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

    • Marked as answer by Anjaneyulu ram Thursday, October 25, 2012 2:30 PM
    Thursday, October 25, 2012 2:13 PM
  • Hi Ram,

    Try as follows,

    CREATE TABLE T_ACTIVITY_ERRORS (ACTIVITY_NUMBER INT,ERROR_ID INT, ERROR_COMMENTS CHAR(10))
    INSERT INTO T_ACTIVITY_ERRORS VALUES(1,1,'AA'),(2,2,'WEER')
    
    
    ALTER TABLE	T_ACTIVITY_ERRORS ADD ACTIVITY_ERROR_ID INT IDENTITY(1,1)
    
    SELECT * FROM  T_ACTIVITY_ERRORS


    Thanks
    Manish

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

    • Marked as answer by Anjaneyulu ram Thursday, October 25, 2012 2:29 PM
    Thursday, October 25, 2012 2:16 PM

All replies

  • Hi Ram,

    Please refer this example,

    CREATE TABLE ManishK (Data INT)
    Insert into ManishK values(1),(2)
    SELECT * FROM ManishK order by data
    
    ALTER TABLE	ManishK ADD RowID INT IDENTITY(1,1)


    Thanks
    Manish

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

    • Marked as answer by Anjaneyulu ram Thursday, October 25, 2012 2:30 PM
    Thursday, October 25, 2012 2:13 PM
  • Try below:

    ALTER TABLE [TABLE NAME] ADD
    	ID int NOT NULL IDENTITY (1, 1)

    Thursday, October 25, 2012 2:16 PM
  • Hi Ram,

    Try as follows,

    CREATE TABLE T_ACTIVITY_ERRORS (ACTIVITY_NUMBER INT,ERROR_ID INT, ERROR_COMMENTS CHAR(10))
    INSERT INTO T_ACTIVITY_ERRORS VALUES(1,1,'AA'),(2,2,'WEER')
    
    
    ALTER TABLE	T_ACTIVITY_ERRORS ADD ACTIVITY_ERROR_ID INT IDENTITY(1,1)
    
    SELECT * FROM  T_ACTIVITY_ERRORS


    Thanks
    Manish

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

    • Marked as answer by Anjaneyulu ram Thursday, October 25, 2012 2:29 PM
    Thursday, October 25, 2012 2:16 PM