如何使用SELECT INTO复制表但忽略IDENTITY属性？

create table with_id (
 id int identity(1,1),
 val varchar(30)
);

#1 楼

来自联机丛书


new_table的格式通过评估选择列表中的表达式来确定。 new_table中的列是按选择列表指定的顺序创建的。 new_table中的每一列与选择列表中的相应表达式具有相同的名称，数据类型，可空性和值。除非在“备注”部分中的“使用身份列”中定义的条件下，否则将转移列的IDENTITY属性。 br />当将现有的Identity列选择到新表中时，除非满足以下条件之一，否则新列将继承IDENTITY属性：


SELECT语句包含一个联接，GROUP BY子句或聚合函数。
使用UNION连接多个SELECT语句。
在选择列表中多次列出了identity列。
identity列是一部分
Identity列来自远程数据源。

如果满足以下任一条件，则创建该列NOT NULL而不继承IDENTITY属性。如果新表中需要标识列，但该列不可用，或者您想要的种子或增量值与源标识列不同，请使用IDENTITY函数在选择列表中定义该列。请参阅下面的示例部分中的“使用IDENTITY函数创建标识列”。


因此...从理论上讲，您可以摆脱：

select id, val 
into copy_from_with_id_2 
from with_id

union all

select 0, 'test_row' 
where 1 = 0;

注释此代码以对其进行解释很重要，以免下次有人查看时将其删除。

#2 楼

受Erics答案的启发，我发现以下解决方案仅取决于表名，而不使用任何特定的列名：

select * into without_id from with_id where 1 = 0
union all
select * from with_id where 1 = 0
;
insert into without_id select * from with_id;

编辑

甚至可以将其改进为

select * into without_id from with_id
union all
select * from with_id where 1 = 0
;

#3 楼

您可以使用联接一口气创建和填充新表：由于这是一个外部联接，因此也不会消除左侧的行。最后，因为这是一个联接，所以消除了IDENTITY属性。因此，仅选择左侧的列，将产生dbo.TableWithIdentity的精确副本，仅在数据方面，即具有IDENTITY属性

话虽如此，Max Vernon在评论中提出了一个有效的观点，值得牢记。如果查看上述查询的执行计划：



，您会注意到在执行计划中仅提及了源表一次。优化程序已消除了另一种情况。版本的SQL Server，也许可以弄清楚IDENTITY属性也不需要删除，因为根据查询计划，源行集中不再有另一个IDENTITY列。这意味着上述查询可能会在某些时候停止按预期的方式工作。

但是，正如ypercubeᵀᴹ正确指出的那样，到目前为止，该手册已明确指出如果存在联接，则IDENTITY属性为未保留：


当将现有标识列选择到新表中时，新列将继承IDENTITY属性，除非SELECT语句包含连接。


因此，只要手册不断提及它，我们就可以放心，其行为将保持不变。

对Shaneis和ypercube表示敬意用于在聊天中显示相关主题。

#4 楼

尝试使用此代码。

#5 楼

只是为了展示一种不同的方式：

您可以使用链接服务器。

SELECT * 
INTO without_id 
FROM [linked_server].[source_db].dbo.[with_id];

您可以使用以下方法临时创建到本地服务器的链接服务器：点，您将运行select * into代码，并引用localserver链接服务器的四部分名称： ：

DECLARE @LocalServer SYSNAME 
SET @LocalServer = @@SERVERNAME;
EXEC master.dbo.sp_addlinkedserver @server = N'localserver'
    , @srvproduct = ''
    , @provider = 'SQLNCLI'
    , @datasrc = @LocalServer;
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'localserver'
    , @useself = N'True'
    , @locallogin = NULL
    , @rmtuser = NULL
    , @rmtpassword = NULL;

或者，您可以使用localserver语法

SELECT * 
INTO without_id 
FROM [localserver].[source_db].dbo.[with_id];

#6 楼

如果select语句包含联接，则不会传输identity属性，因此

select a.* into without_id from with_id a inner join with_id b on 1 = 0;

也将给出所需的行为（复制的id列不保留IDENTITY属性，但是，它的副作用是根本不复制任何行（与其他方法一样），因此您需要执行以下操作：

insert into without_id select * from with_id;

（感谢AakashM！）

#7 楼

最简单的方法是使列成为表达式的一部分。

示例：
如果表dbo.Employee在ID列上具有标识，则在下面的示例中，临时表#t在ID列上也具有IDENTITY。

--temp table has IDENTITY
select ID, Name 
into #t
from dbo.Employee

对此进行更改以将表达式应用于ID，您#t将不再在ID列上具有IDENTITY。在这种情况下，我们对ID列进行简单的添加。

--no IDENTITY
select ID = ID + 0, Name 
into #t
from dbo.Employee

其他数据类型的表达式的其他示例可能包括：convert（），字符串连接或Isnull（）

#8 楼

有时，您想从不知道（或不在乎）该表是否是使用IDENTITY创建的表中插入。它甚至可能不是您正在使用的整数列。在这种情况下，下面的方法将起作用：使它不能为空。 TOP（0）将创建一个空表，然后您可以使用该表将选定的行插入其中。如果需要，还可以在插入数据之前压缩表。

#9 楼

select convert(int, id) as id, val 
into copy_from_with_id_without_id 
from with_id;

将删除身份。

缺点是id可以为空，但您可以添加该约束。

#10 楼

请尝试以下查询：我发现此查询正常工作。这将选择没有标识= 1的列中的数据

DECLARE @Temp NVARCHAR(MAX); 
DECLARE @SQL NVARCHAR(MAX);

SET @Temp = '';
SELECT @Temp = @Temp + COLUMN_NAME + ', ' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='Person' AND  columnProperty(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') != 1 

SET @SQL = 'SELECT ' + SUBSTRING(@Temp, 0, LEN(@Temp)) +' FROM [Person]';
EXECUTE SP_EXECUTESQL @SQL;

您可以从sys.columns表中获取列名详细信息
尝试以下查询：

#11 楼

你不知道select * into保留身份。