How to Create Table with Identity Column

How To Create Table with Identity Column

CREATE TABLE [dbo].[History](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [RequestID] [int] NOT NULL,
    [EmployeeID] [varchar](50) NOT NULL,
    [DateStamp] [datetime] NOT NULL,
 CONSTRAINT [PK_History] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
) ON [PRIMARY]

Adding an identity to an existing column

You can't alter the existing columns for identity.

You have 2 options,

1. Create a new table with identity & drop the existing table

2. Create a new column with identity & drop the existing column

Approach 1. (New table) Here you can retain the existing data values on the newly created identity column. Note that you will lose all data if 'if not exists' is not satisfied, so make sure you put the condition on the drop as well!

CREATE TABLE dbo.Tmp_Names
    (
      Id int NOT NULL
             IDENTITY(1, 1),
      Name varchar(50) NULL
    )
ON  [PRIMARY]
go

SET IDENTITY_INSERT dbo.Tmp_Names ON
go

IF EXISTS ( SELECT  *
            FROM    dbo.Names ) 
    INSERT  INTO dbo.Tmp_Names ( Id, Name )
            SELECT  Id,
                    Name
            FROM    dbo.Names TABLOCKX
go

SET IDENTITY_INSERT dbo.Tmp_Names OFF
go

DROP TABLE dbo.Names
go

Exec sp_rename 'Tmp_Names', 'Names'

Approach 2 (New column) You can’t retain the existing data values on the newly created identity column, The identity column will hold the sequence of number.

Alter Table Names
Add Id_new Int Identity(1, 1)
Go

Alter Table Names Drop Column ID
Go

Exec sp_rename 'Names.Id_new', 'ID', 'Column'

See the following Microsoft SQL Server Forum post for more details:

How to alter column to identity(1,1)

How to have an identity column for a temp table in SQL?

Add this to your query after table declaration

SET IDENTITY_INSERT #t OFF 

This should fix it. The following code works on my machine

CREATE TABLE #t
(
    [ID] [INT] IDENTITY(1,1) NOT NULL,
    [TotalCount] INT,
    [PercentageComplete] NVARCHAR(4000)
)

SET IDENTITY_INSERT #t OFF 

INSERT INTO #t
    SELECT
        totalcount, percentagecomplete 
    FROM
        table_a

How to set identity column to created table in SQL server

ALTER TABLE [UserName] DROP COLUMN [ID];

ALTER TABLE [UserName] 
    ADD [ID] integer identity not null;

Adding an `IDENTITY` column to an existing delta table (Databricks)

Yes, apparently you can't add a generated column. ALTER TABLE syntax doesn't seem to allow that.

As a workaround create a table from scratch and copy data:

CREATE TABLE tname_ (
    <tname columns>,
    id BIGINT GENERATED BY DEFAULT AS IDENTITY
);
INSERT INTO tname_ (<tname columns>) SELECT * FROM tname;
DROP TABLE tname;
ALTER TABLE tname_ RENAME TO tname;

How to copy table data and structure with Identity column and its value to another table in the same db(just change table name)

Try your insert with the column names:

INSERT INTO My_TABLE_NEW ([ID], [OBJECT_ID], [YEAR_MONTH])
        SELECT [ID]
               ,[OBJECT_ID]
              ,[YEAR_MONTH]             
        FROM My_TABLE

---

Related Topics