CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Ryan
Ryan

Reputation: 20116

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?

Upvotes: 0

Views: 1488

Answers (3)

Wellerman
Wellerman

Reputation: 856

You can use the built-in tool sp_rename for this, as long as you are just renaming the table not trying to create a copy of it.

EXEC sp_rename 'My_TABLE', 'My_TABLE_NEW'
GO;

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-rename-transact-sql?view=sql-server-ver15

If you want to create a copy, then you just have to do the following:

SELECT *
INTO My_TABLE_NEW
FROM My_TABLE

Note: If you do this, you will have to re-add any key constraints, computed value columns, etc.

Upvotes: 0

Paul Evans
Paul Evans

Reputation: 1499

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

Upvotes: 2

pwang
pwang

Reputation: 71

From the documentation:

When an existing identity column is selected into a new table, the new column inherits the IDENTITY property, unless one of the following conditions is true:

The SELECT statement contains a join, GROUP BY clause, or aggregate function.

Multiple SELECT statements are joined by using UNION.

The identity column is listed more than one time in the select list.

The identity column is part of an expression.

The identity column is from a remote data source.

That means you can copy the table with SELECT INTO while retaining the identity column and can just add the PK after.

SELECT *
INTO My_TABLE_NEW
FROM My_TABLE

Here is a demo with fiddle.

Upvotes: 0

Related Questions