如何将具有Identity列及其值的表数据和结构复制到同一数据库中的另一个表中(只需更改表名)

Question

我在将表数据和结构复制到另一个表时遇到了问题，因为我想保留Identity列的Id列并保留其oridinal值，而不是从1 开始

我使用下面的sql插入从MY_TABLE到MY_TABLE_NEW的除ID列之外的所有数据，因为它有错误，表示

只有在使用列列表并且IDENTITY_INSERT为ON时，才能为表'My_table_NEW'中的标识列指定显式值。

但我已经将其设置为如下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

问题出在哪里？

Answer 1

尝试插入列名：

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

Answer 2

来自文档：

当将现有标识列选择到新表中时，新列将继承identity属性，除非以下条件之一为真：

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.

这意味着您可以在保留标识列的同时复制具有SELECT INTO的表，并且可以在之后添加PK。

SELECT *
INTO My_TABLE_NEW
FROM My_TABLE

这是一个小提琴演示。

Answer 3

您可以使用内置工具sp_rename来实现这一点，只要您只是重命名表，而不是试图创建它的副本。

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-服务器-ver15

如果你想创建一个副本，那么你只需要做以下事情：

SELECT *
INTO My_TABLE_NEW
FROM My_TABLE

注意：如果您这样做，您将不得不重新添加任何键约束、计算值列等。