Set Identity field to existing column in sql server

  • Hi Friends,

    I am new to sql server.

    I have a table called Table1.

    CREATE TABLE [dbo].Table1(

    [ID] [int] NOT NULL,

    [Name] [varchar](25) NULL,

    ) ON [PRIMARY]

    I am entering both ID,Name values. I have almost 28965 rows. Now i want to enter only Name field value and ID should be Identity field and that should be starts from 28966. How can i change this? How to set the identity option to existing column? Please advice.

    Thanks

    Swetha.

  • You can't change an existing column to identity, but you can have two options:

    1.create a new table with identity column, then insert value from your old table(Table1)

    2.create a column in existing table(Table1) with identity, update this field with data from old int column, then drop the old int column and rename new identity column with previous int column name.

    🙂

  • Hello,

    BEGIN TRANSACTION

    SET QUOTED_IDENTIFIER ON

    SET ARITHABORT ON

    SET NUMERIC_ROUNDABORT OFF

    SET CONCAT_NULL_YIELDS_NULL ON

    SET ANSI_NULLS ON

    SET ANSI_PADDING ON

    SET ANSI_WARNINGS ON

    COMMIT

    BEGIN TRANSACTION

    GO

    CREATE TABLE dbo.Tmp_Table1

    (

    ID int NOT NULL IDENTITY (28966, 1),

    Name varchar(25) NULL

    ) ON [PRIMARY]

    GO

    SET IDENTITY_INSERT dbo.Tmp_Table1 ON

    GO

    IF EXISTS(SELECT * FROM dbo.Table1)

    EXEC('INSERT INTO dbo.Tmp_Table1 (ID, Name)

    SELECT ID, Name FROM dbo.Table1 WITH (HOLDLOCK TABLOCKX)')

    GO

    SET IDENTITY_INSERT dbo.Tmp_Table1 OFF

    GO

    DROP TABLE dbo.Table1

    GO

    EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'

    GO

    ALTER TABLE dbo.Table1 ADD CONSTRAINT

    PK_Table1 PRIMARY KEY CLUSTERED

    (

    ID

    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    GO

    COMMIT

    😉

    Jeff.

  • Thank you all.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply