Select Into From, Create table without identity property

Question

我有这样的存储过程，它在为任何表插入命令后执行

Create Procedure [dbo].[HistoryInsert](
@TableName  nVarchar(500),
@RecordId   bigInt
)
As
declare @Query  nVarChar(max)
if Not Exists (Select   Top 1 1
From    Information_schema.tables
Where   Table_Name  = @TableName + 'History')
Set @Query  = 'Select  *  Into ' + @TableName + 'History FROM ' + @TableName
Else
Set @Query  = 'Insert Into ' + @TableName  + 'History Select *   FROM ' + @TableName
Exec(@Query)
Exec(@Query)

当第一次执行此过程时，创建了历史记录表。当第二次执行此过程时，插入失败，因为创建的表有标识列。如何从表中为所有列选择，而不为列选择递增标识属性。

Answer 1

有一些生命破解

select *
into #tmp_table
from Table
union all
select *
from Table 
where 1<>1

使用并集也反对SQL Endgine继承约束和标识。

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.

Answer 2

您的代码看起来像是试图保留数据更改的历史记录。考虑使用变更数据捕获，而不是推出自己的解决方案。

允许插入指定了identity列的一种方法是identity_insert:

SET IDENTITY_INSERT TableName ON

你可以在你的第二个高管身上打开它。由于您要复制整个表，因此在复制之前必须清除历史表，例如使用truncate:

Set @Query  = 
'truncate table ' + @TableName  + 'History; ' +
'set identity_insert ' + @TableName 'History on; ' +
'Insert Into ' + @TableName  + 'History Select *   FROM ' + @TableName '; ' +
'set identity_insert ' + @TableName 'History off; '

一个更好的解决方案是修改第一个exec以创建一个没有identity列的表。我还没有找到切实可行的方法。SQL Server要求您删除该列并重新创建它，这将导致非常麻烦的动态SQL。

Answer 3

Andomar是对的，如果你可以使用变更数据捕获，你应该这样做。然而，如果你被一个不支持它的版本卡住了，你仍然可以解决这个问题。

根据微软的说法。。。

当将现有标识列选择到新表中时列继承IDENTITY属性，除非出现以下情况之一条件是真的：

SELECT语句包含联接、GROUP BY子句或聚合函数

多个SELECT语句通过使用UNION联接

标识列在选择列表中多次列出

标识列是表达式的一部分

标识列来自远程数据源

因此，例如，如果您知道标识列的名称，则可以在不需要知道任何其他列的情况下执行类似操作：

Exec('select ' + @identityColName + ' as id_temp123, * into ' + @TableName  + 'History from ' + @TableName)
Exec('alter table ' + @TableName  + ' drop column id_temp123')

这也保留了列顺序。

您可以通过在信息架构中查找标识列(如果有)来以编程方式查找标识列。例如：

select TABLE_NAME + '.' + COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS 
where 
TABLE_SCHEMA = 'dbo' and 
TABLE_NAME = 'MyTable' and 
columnProperty(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1

Answer 4

尝试以下查询：我发现这个查询工作正常。这将选择没有标识的列中的数据=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表中获取列名详细信息

尝试以下查询：

SELECT * FROM SYS.COLUMNS 
WHERE object_id = OBJECT_ID('dbo.TableName') 
AND [Name] <> 'ColumnName'
DECLARE @sql as VARCHAR(8000)
SET @sql = 'SELECT '
SELECT @sql += [Name] + ', ' FROM SYS.COLUMNS 
WHERE object_id = OBJECT_ID('dbo.TableName') 
AND [Name] <> 'ColumnName'
SELECT @sql += ' FROM Dbo.TableName'
EXEC(@sql)