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

I have trouble in copying table data and structure to another table since I want to keep the Id column of Identity column and keep its oridinal value instead of starting from 1

I use below sql to insert all data except the ID column from MY_TABLE to MY_TABLE_NEW since it has error saying that

Only when the column list is used and IDENTITY_INSERT is ON, an explicit value can be specified for the identity column in the table'My_TABLE_NEW'.

But I have set it like below sql:

IF NOT EXISTS (select * from sys.objects where name = 'My_TABLE_NEW')      BEGIN          CREATE TABLE [dbo].[My_TABLE_NEW](              [ID] [int] IDENTITY(1,1) NOT NULL,              [OBJECT_ID] [int] NOT NULL,              [YEAR_MONTH] [int] NOT NULL,                         CONSTRAINT [PK_My_TABLE_NEW] 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]          ) ON [PRIMARY]      END  GO    IF  EXISTS (select * from sys.objects where name = 'My_TABLE_NEW')  BEGIN      SET IDENTITY_INSERT My_TABLE_NEW ON      INSERT INTO My_TABLE_NEW          SELECT [ID]                 ,[OBJECT_ID]                ,[YEAR_MONTH]                          FROM My_TABLE     SET IDENTITY_INSERT My_TABLE_NEW OFF  END  GO  

What is the problem?

https://stackoverflow.com/questions/67310723/how-to-copy-table-data-and-structure-with-identity-column-and-its-value-to-anoth April 29, 2021 at 11:40AM