SQL Server Performance

I want to add a identity column to a already existing column in a table

Discussion in 'General Developer Questions' started by mpolaiah, Feb 6, 2009.

  1. mpolaiah New Member

    Hi,

    I want to add a identity column to a already existing column in a table which has some unique numbers in it. It is possible through enterprise manager but i want to generate a script to send to my client so that he just runs it and identity is added to the column and there is no data loss. I tried using Alter table but it didnt work. Please let me know if anyone has done this.

    Thanks,
    polaiah
  2. Adriaan New Member

    --copy the contents of the table into a temp table
    SELECT *
    INTO #temp
    FROM owner.mytable
    --empty the table
    DELETE FROM owner.mytable
    --drop existing column
    ALTER TABLE owner.mytable DROP existing_column
    GO
    --create identity column
    ALTER TABLE owner.mytable ADD COLUMN identity_column IDENTITY (1, 1)
    GO
    --allow insert of existing values on identity column
    SET IDENTITY_INSERT owner.mytable ON
    --copy contents of temp table into table
    INSERT INTO owner.mytable SELECT * FROM #temp
    --disallow insert of existing values on identity column
    SET IDENTITY_INSERT owner.mytable OFF
    --clean up after yourself
    DROP TABLE #temp
  3. rohit2900 Member

    [quote user="Adriaan"]
    --copy contents of temp table into table
    INSERT INTO owner.mytable SELECT * FROM #temp
    [/quote]
    Just one correction while inserting into mytable include the columns in both insert & select statement, which will enable you to map the newly added identity column with the existing column in #temp.

Share This Page