locked
Adding an Identity Later T-SQL RRS feed

  • Question

  • Good Day All

    i have a Table with the ID Field that was not an identity Field. This Field has not Duplicates. Now later i want to Change this numeric Field as an Identity Field like

    --STEP 7 ADD THE IDENTITY BACK IN TABLE MTM_ACTV_STAFF
    ALTER TABLE [New_Jaco].[dbo].[MTM_ACTV_STAFF]
    ALTER COLUMN [ID] IDENTITY(1,1)



    but i get an Error that says
      Msg 156, Level 15, State 1, Line 3
    Incorrect syntax near the keyword 'IDENTITY'. 



    Vuyiswa Maseko
    Friday, December 4, 2009 8:20 AM

Answers

  • Vuyiswa,

    That cannot be done with a simple ALTER.  Following shows a table rebuilding solution.

    You can change the column property to identity though in Object Explorer, Design Table. SSMS carries out the table "makeover" like below.

    -- T-SQL making a column an identity column
    USE tempdb;
    SELECT ProductID=CONVERT(int, ProductID), ProductName=Name,
    ListPrice, Color
    INTO Product
    FROM AdventureWorks2008.Production.Product
    GO
    
    BEGIN TRANSACTION
    GO
    CREATE TABLE dbo.Tmp_Product
    	(
    	ProductID int NOT NULL IDENTITY (1, 1),
    	ProductName nvarchar(50) NOT NULL,
    	ListPrice money NOT NULL,
    	Color nvarchar(15) NULL
    	)  ON [PRIMARY]
    GO
    SET IDENTITY_INSERT dbo.Tmp_Product ON
    GO
    IF EXISTS(SELECT * FROM dbo.Product)
    	 EXEC('INSERT INTO dbo.Tmp_Product (ProductID, ProductName, ListPrice, Color)
    		SELECT ProductID, ProductName, ListPrice, Color FROM dbo.Product WITH (HOLDLOCK TABLOCKX)')
    GO
    SET IDENTITY_INSERT dbo.Tmp_Product OFF
    GO
    DROP TABLE dbo.Product
    GO
    EXECUTE sp_rename N'dbo.Tmp_Product', N'Product', 'OBJECT' 
    GO
    COMMIT
    GO
    EXEC sp_help Product
    GO
    /*
    Identity	Seed	Increment	Not For Replication
    ProductID	1	1	0
    */
    DROP TABLE dbo.Product

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012
    Friday, December 4, 2009 9:23 AM

All replies

  • You need to assign the column a numeric data type e.g. INT.

    --STEP 7 ADD THE IDENTITY BACK IN TABLE MTM_ACTV_STAFF
    
    ALTER TABLE [New_Jaco].[dbo].[MTM_ACTV_STAFF] ALTER COLUMN [ID] INT IDENTITY (1,1);
    • Proposed as answer by Kalman Toth Friday, December 4, 2009 8:33 AM
    • Unproposed as answer by Kalman Toth Friday, December 4, 2009 9:22 AM
    Friday, December 4, 2009 8:31 AM
  • Good Day  Orion Pax


    I tried that and it did not work same Error.

    Thank you for your reply

    Vuyiswa Maseko
    Friday, December 4, 2009 8:40 AM
  • Sorry I read it as an add column script. Not very observant of me. You cannot change an existing column to an identity column. You can only add a new column with the identity attribute. So you have two options:

    • Renaming the old column, adding a new column then dropping the old column. This will however add the column to the end of the table (last ordinal position) and if the ID column is a primary key, it needs to be dropped and recreated.
    -- rename existing column
    EXEC sp_rename 'MTM_ACTV_STAFF.ID', 'ID_OLD', 'COLUMN';
    
    -- add new ID column as identity
    ALTER TABLE MTM_ACTV_STAFF ADD ID INT IDENTITY (1, 1);
    
    -- drop old column
    ALTER TABLE MTM_ACTV_STAFF DROP COLUMN ID_OLD;
    • Renaming the table (e.g. temp_MTM_ACTIV_STAFF), recreating the MTM_ACTIV_STAFF table with the ID column as IDENTITY and re-populating the table from the temp_MTM_ACTIV_STAFF table. I will need the MTM_ACTIV_STAFF table creation script to supply you a workable script. I can however provide you a sample.
    -- rename MTM_ACTV_STAFF to temp_MTM_ACTV_STAFF table 
    EXEC sp_rename 'MTM_ACTV_STAFF', 'temp_MTM_ACTV_STAFF';
    
    -- create MTM_ACTV_STAFF table
    CREATE TABLE MTM_ACTV_STAFF (
    	ID INT IDENTITY(1, 1) PRIMARY KEY
    ,	OtherColumn	VARCHAR(10) NOT NULL
    -- add other columns here
    ); 
    
    --- repopulate MTM_ACTV_STAFF table 
    INSERT INTO MTM_ACTV_STAFF (OtherColumn)
    SELECT	OtherColumn
    FROM	temp_MTM_ACTV_STAFF;
    
    -- drop temp table
    DROP TABLE temp_MTM_ACTV_STAFF;
    Hope this helps.
    • Proposed as answer by Naomi N Friday, April 22, 2011 2:09 PM
    Friday, December 4, 2009 9:16 AM
  • Vuyiswa,

    That cannot be done with a simple ALTER.  Following shows a table rebuilding solution.

    You can change the column property to identity though in Object Explorer, Design Table. SSMS carries out the table "makeover" like below.

    -- T-SQL making a column an identity column
    USE tempdb;
    SELECT ProductID=CONVERT(int, ProductID), ProductName=Name,
    ListPrice, Color
    INTO Product
    FROM AdventureWorks2008.Production.Product
    GO
    
    BEGIN TRANSACTION
    GO
    CREATE TABLE dbo.Tmp_Product
    	(
    	ProductID int NOT NULL IDENTITY (1, 1),
    	ProductName nvarchar(50) NOT NULL,
    	ListPrice money NOT NULL,
    	Color nvarchar(15) NULL
    	)  ON [PRIMARY]
    GO
    SET IDENTITY_INSERT dbo.Tmp_Product ON
    GO
    IF EXISTS(SELECT * FROM dbo.Product)
    	 EXEC('INSERT INTO dbo.Tmp_Product (ProductID, ProductName, ListPrice, Color)
    		SELECT ProductID, ProductName, ListPrice, Color FROM dbo.Product WITH (HOLDLOCK TABLOCKX)')
    GO
    SET IDENTITY_INSERT dbo.Tmp_Product OFF
    GO
    DROP TABLE dbo.Product
    GO
    EXECUTE sp_rename N'dbo.Tmp_Product', N'Product', 'OBJECT' 
    GO
    COMMIT
    GO
    EXEC sp_help Product
    GO
    /*
    Identity	Seed	Increment	Not For Replication
    ProductID	1	1	0
    */
    DROP TABLE dbo.Product

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012
    Friday, December 4, 2009 9:23 AM
  • Thank you very much guys for you help, this Helped me.




    Vuyiswa Maseko
    Friday, December 4, 2009 11:07 AM
  • yaah this helped me too, thanks all for the gr8 help & correct answer
    maqk loves to code
    Friday, April 22, 2011 10:27 AM