如何在SQL Server 2005中去除列的IDENTITY属性

Question

如何在SQL Server 2005中去除列的IDENTITY属性

sqlsql-server-2005t-sql

10

我想要在SQL Server 2005中将带有标识列的表中的数据插入到临时表中。

对应的T-SQL代码如下：

-- Create empty temp table
SELECT *
INTO #Tmp_MyTable
FROM MyTable
WHERE 1=0
...
WHILE ...
BEGIN
    ...
    INSERT INTO #Tmp_MyTable
    SELECT TOP (@n) *
    FROM MyTable
    ...

END

上面的代码创建了一个带有标识列的#Tmp_Table，然后插入操作失败并显示错误信息：“只有在使用列列表且 IDENTITY_INSERT 为 ON 时，才能为表 '#Tmp_MyTable' 中的标识列指定显式值。”

在TSQL中是否有一种方法可以在不显式列出所有列的情况下删除临时表中列的标识属性？我想要明确地使用“SELECT *”，这样如果将来添加了新列到MyTable，代码仍将继续工作。

我认为删除和重新创建列会改变其位置，从而无法使用SELECT *。

更新：我尝试按照一个回答中提供的方法使用IDENTITY_INSERT，但它没有起作用-请参见下面的示例。我做错了什么？

-- Create test table
CREATE TABLE [dbo].[TestTable](
    [ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NULL,
 CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)
) 
GO
-- Insert some data
INSERT INTO TestTable
(Name)
SELECT 'One'
UNION ALL
SELECT 'Two'
UNION ALL
SELECT 'Three'
GO
-- Create empty temp table
SELECT *
INTO #Tmp
FROM TestTable
WHERE 1=0

SET IDENTITY_INSERT #Tmp ON -- I also tried OFF / ON
INSERT INTO #Tmp
SELECT TOP 1 * FROM TestTable

SET IDENTITY_INSERT #Tmp OFF 
GO
-- Drop test table
DROP TABLE [dbo].[TestTable]
GO

请注意错误信息"An explicit value for the identity column in table '#TmpMyTable' can only be specified when a column list is used and IDENTITY_INSERT is ON." - 如上所述，我不想使用列列表。

更新2： 尝试了Mike的建议，但出现了相同的错误：

-- Create empty temp table
SELECT *
INTO #Tmp
FROM (SELECT
      m1.*
      FROM TestTable                 m1
          LEFT OUTER JOIN TestTable  m2 ON m1.ID=m2.ID
      WHERE 1=0
 ) dt

INSERT INTO #Tmp
SELECT TOP 1 * FROM TestTable

我想这么做的原因是：MyTable是一个暂存表，其中可能包含大量要合并到另一个表中的行。我想循环处理来自暂存表的行，在每个事务中处理N行，将它们插入/更新到我的主表中，并从暂存表中删除它们。我意识到还有其他实现方法。

更新3

我无法使Mike的解决方案工作，但它提出了以下解决方案，该解决方案可以工作：在非标识列前缀并删除标识列：

SELECT CAST(1 AS NUMERIC(18,0)) AS ID2, *
INTO #Tmp
FROM TestTable
WHERE 1=0
ALTER TABLE #Tmp DROP COLUMN ID

INSERT INTO #Tmp
SELECT TOP 1 * FROM TestTable

在这种情况下，为临时表仅存储键的Mike建议也是一个好建议，尽管有些原因我更喜欢将所有列都存储在临时表中。

- Joe

它对我有效，看看我的答案... - KM.

你在第三次更新中提供的解决方案对我非常有效。我试图将UPDATE的OUTPUT输出到一个临时表中，而不指定列（大约100个！）。 - apc

5个回答

3

如果您只是按照您所描述的方式处理行，那么将前N个主键值选择到临时表中是否更好呢？

CREATE TABLE #KeysToProcess
(
     TempID    int  not null primary key identity(1,1)
    ,YourKey1  int  not null
    ,YourKey2  int  not null
)

INSERT INTO #KeysToProcess (YourKey1,YourKey2)
SELECT TOP n YourKey1,YourKey2  FROM MyTable

这些关键字不应该经常更改（希望如此），但是其他列可以以这种方式进行更改而不会造成任何伤害。

获取插入的 @@ROWCOUNT，您可以在 TempID 上进行简单的循环，其中它将从 1 到 @@ROWCOUNT。

和/或

只需将 #KeysToProcess 加入到您的 MyKeys 表中即可继续前进，无需复制所有数据。

这在我的 SQL Server 2005 上运行良好，其中 MyTable.MyKey 是标识列。

-- Create empty temp table
SELECT *
INTO #TmpMikeMike
FROM (SELECT
      m1.*
      FROM MyTable                 m1
          LEFT OUTER JOIN MyTable  m2 ON m1.MyKey=m2.MyKey
      WHERE 1=0
 ) dt

INSERT INTO #TmpMike
SELECT TOP 1 * FROM MyTable

SELECT * from #TmpMike

编辑
这个可以正常工作，没有错误...

-- Create empty temp table
SELECT *
INTO #Tmp_MyTable
FROM (SELECT
          m1.*
          FROM MyTable                 m1
              LEFT OUTER JOIN MyTable  m2 ON m1.KeyValue=m2.KeyValue
          WHERE 1=0
     ) dt
...
WHILE ...
BEGIN
    ...
    INSERT INTO #Tmp_MyTable
    SELECT TOP (@n) *
    FROM MyTable
    ...

END

然而，你真正的问题是什么？ 为什么需要在这个临时表中插入 "*" 的同时进行循环？您可以改变策略并提出更好的算法。

- mike

对我没用。我尝试按照你建议的修改我的重现，见上文。 - Joe

嵌套查询的另一个选项是 SELECT m1.* FROM MyTable m1 LEFT OUTER JOIN MyTable m2 ON 1 = 1 WHERE 1 = 0 然后您就不需要知道表列结构的任何信息。 - Davin Studer

1

编辑根据达伦的建议来切换 IDENTITY_INSERT，显然是更优雅的方法。在我的情况下，我需要消除标识列，以便将选定的数据重新插入源表中。

我解决这个问题的方式是像你一样创建临时表，明确删除标识列，然后动态构建 SQL 语句，使得我有一个排除标识列的列列表（就像你的情况一样，所以如果架构发生变化，该过程仍将起作用），然后执行 SQL，这是一个示例

declare @ret int
Select * into #sometemp from sometable
Where
id = @SomeVariable

Alter Table #sometemp Drop column SomeIdentity 

Select @SelectList = ''
Select @SelectList = @SelectList 
+ Coalesce( '[' + Column_name + ']' + ', ' ,'')
from information_schema.columns
where table_name = 'sometable'
and Column_Name <> 'SomeIdentity'

Set @SelectList = 'Insert into sometable (' 
+ Left(@SelectList, Len(@SelectList) -1) + ')'
Set @SelectList = @SelectList 
+ ' Select * from #sometemp '
exec @ret  =  sp_executesql  @selectlist

- cmsjr

0

我已经编写了这个过程，将许多答案编译在一起，以自动快速地删除列标识：

CREATE PROCEDURE dbo.sp_drop_table_identity @tableName VARCHAR(256) AS
BEGIN
    DECLARE @sql VARCHAR (4096);
    DECLARE @sqlTableConstraints VARCHAR (4096);
    DECLARE @tmpTableName VARCHAR(256) = @tableName + '_noident_temp';

    BEGIN TRANSACTION

    -- 1) Create temporary table with edentical structure except identity
    -- Idea borrowed from https://dev59.com/T3VD5IYBdhLWcg3wTZ1m
    -- modified to ommit Identity and honor all constraints, not primary key only!
    SELECT
        @sql = 'CREATE TABLE [' + so.name + '_noident_temp] (' + o.list + ')'
        + ' ' + j.list
    FROM sysobjects so
    CROSS APPLY (
        SELECT
            ' [' + column_name + '] '
            + data_type
            + CASE data_type
                WHEN 'sql_variant' THEN ''
                WHEN 'text' THEN ''
                WHEN 'ntext' THEN ''
                WHEN 'xml' THEN ''
                WHEN 'decimal' THEN '(' + CAST(numeric_precision as VARCHAR) + ', ' + CAST(numeric_scale as VARCHAR) + ')'
                ELSE COALESCE('(' + CASE WHEN character_maximum_length = -1 THEN 'MAX' ELSE CAST(character_maximum_length as VARCHAR) END + ')', '')
            END
            + ' '
            /* + case when exists ( -- Identity skip
            select id from syscolumns
            where object_name(id)=so.name
            and name=column_name
            and columnproperty(id,name,'IsIdentity') = 1
            ) then
            'IDENTITY(' +
            cast(ident_seed(so.name) as varchar) + ',' +
            cast(ident_incr(so.name) as varchar) + ')'
            else ''
            end + ' ' */
            + CASE WHEN IS_NULLABLE = 'No' THEN 'NOT ' ELSE '' END
            + 'NULL'
            + CASE WHEN information_schema.columns.column_default IS NOT NULL THEN ' DEFAULT ' + information_schema.columns.column_default ELSE '' END
            + ','
        FROM
            INFORMATION_SCHEMA.COLUMNS
        WHERE table_name = so.name
        ORDER BY ordinal_position
        FOR XML PATH('')
    ) o (list)
    CROSS APPLY(
        SELECT
            CHAR(10) + 'ALTER TABLE ' + @tableName + '_noident_temp ADD ' + LEFT(alt, LEN(alt)-1)
        FROM(
            SELECT
                CHAR(10)
                + ' CONSTRAINT ' + tc.constraint_name  + '_ni ' + tc.constraint_type + ' (' + LEFT(c.list, LEN(c.list)-1) + ')'
                + COALESCE(CHAR(10) + r.list, ', ')
            FROM
                information_schema.table_constraints tc
                CROSS APPLY(
                    SELECT
                        '[' + kcu.column_name + '], '
                    FROM
                        information_schema.key_column_usage kcu
                    WHERE
                        kcu.constraint_name = tc.constraint_name
                    ORDER BY
                        kcu.ordinal_position
                    FOR XML PATH('')
                ) c (list)
                OUTER APPLY(
                    -- https://dev59.com/vm865IYBdhLWcg3wQMMD
                    SELECT
                        '  REFERENCES [' + kcu1.constraint_schema + '].' + '[' + kcu2.table_name + ']' + '([' + kcu2.column_name + ']) '
                        + CHAR(10)
                        + '    ON DELETE ' + rc.delete_rule
                        + CHAR(10)
                        + '    ON UPDATE ' + rc.update_rule + ', '
                    FROM information_schema.referential_constraints as rc
                        JOIN information_schema.key_column_usage as kcu1 ON (kcu1.constraint_catalog = rc.constraint_catalog AND kcu1.constraint_schema = rc.constraint_schema AND kcu1.constraint_name = rc.constraint_name)
                        JOIN information_schema.key_column_usage as kcu2 ON (kcu2.constraint_catalog = rc.unique_constraint_catalog AND kcu2.constraint_schema = rc.unique_constraint_schema AND kcu2.constraint_name = rc.unique_constraint_name AND kcu2.ordinal_position = KCU1.ordinal_position)
                    WHERE
                        kcu1.constraint_catalog = tc.constraint_catalog AND kcu1.constraint_schema = tc.constraint_schema AND kcu1.constraint_name = tc.constraint_name
                ) r (list)
            WHERE tc.table_name = @tableName
            FOR XML PATH('')
        ) a (alt)
    ) j (list)
    WHERE
        xtype = 'U'
    AND name NOT IN ('dtproperties')
    AND so.name = @tableName

    SELECT @sql as '1) @sql';
    EXECUTE(@sql);

    -- 2) Obtain current back references on our table from others to reenable it later
    -- https://dev59.com/vm865IYBdhLWcg3wQMMD
    SELECT
        @sqlTableConstraints = (
            SELECT
                'ALTER TABLE [' + kcu1.constraint_schema + '].' + '[' + kcu1.table_name + ']'
                + ' ADD CONSTRAINT ' + kcu1.constraint_name + '_ni FOREIGN KEY ([' + kcu1.column_name + '])'
                + CHAR(10)
                + '  REFERENCES ['  + kcu2.table_schema + '].[' + kcu2.table_name + ']([' + kcu2.column_name + '])'
                + CHAR(10)
                + '    ON DELETE ' + rc.delete_rule
                + CHAR(10)
                + '    ON UPDATE ' + rc.update_rule + ' '
            FROM information_schema.referential_constraints as rc
                JOIN information_schema.key_column_usage as kcu1 ON (kcu1.constraint_catalog = rc.constraint_catalog AND kcu1.constraint_schema = rc.constraint_schema AND kcu1.constraint_name = rc.constraint_name)
                JOIN information_schema.key_column_usage as kcu2 ON (kcu2.constraint_catalog = rc.unique_constraint_catalog AND kcu2.constraint_schema = rc.unique_constraint_schema AND kcu2.constraint_name = rc.unique_constraint_name AND kcu2.ordinal_position = KCU1.ordinal_position)
            WHERE
                kcu2.table_name = 'department'
            FOR XML PATH('')
        );
    SELECT @sqlTableConstraints as '8) @sqlTableConstraints';
    -- Execute at end

    -- 3) Drop outer references for switch (structure must be identical: http://msdn.microsoft.com/en-gb/library/ms191160.aspx) and rename table
    SELECT
        @sql = (
            SELECT
                ' ALTER TABLE [' + kcu1.constraint_schema + '].' + '[' + kcu1.table_name + '] DROP CONSTRAINT ' + kcu1.constraint_name
            FROM information_schema.referential_constraints as rc
                JOIN information_schema.key_column_usage as kcu1 ON (kcu1.constraint_catalog = rc.constraint_catalog AND kcu1.constraint_schema = rc.constraint_schema AND kcu1.constraint_name = rc.constraint_name)
                JOIN information_schema.key_column_usage as kcu2 ON (kcu2.constraint_catalog = rc.unique_constraint_catalog AND kcu2.constraint_schema = rc.unique_constraint_schema AND kcu2.constraint_name = rc.unique_constraint_name AND kcu2.ordinal_position = KCU1.ordinal_position)
            WHERE
                kcu2.table_name = @tableName
            FOR XML PATH('')
        );
    SELECT @sql as '3) @sql'
    EXECUTE (@sql);

    -- 4) Switch partition
    -- http://www.calsql.com/2012/05/removing-identity-property-taking-more.html
    SET @sql = 'ALTER TABLE ' + @tableName + ' switch partition 1 to ' + @tmpTableName;
    SELECT @sql as '4) @sql';
    EXECUTE(@sql);

    -- 5) Rename real old table to bak
    SET @sql = 'EXEC sp_rename ' + @tableName + ', ' + @tableName + '_bak';
    SELECT @sql as '5) @sql';
    EXECUTE(@sql);

    -- 6) Rename temp table to real
    SET @sql = 'EXEC sp_rename ' + @tmpTableName + ', ' + @tableName;
    SELECT @sql as '6) @sql';
    EXECUTE(@sql);

    -- 7) Drop bak table
    SET @sql = 'DROP TABLE ' + @tableName + '_bak';
    SELECT @sql as '7) @sql';
    EXECUTE(@sql);

    -- 8) Create again doped early constraints
    SELECT @sqlTableConstraints as '8) @sqlTableConstraints';
    EXECUTE(@sqlTableConstraints);


    -- It still may fail if there references from objects with WITH CHECKOPTION
    -- it may be recreated - https://dev59.com/x3I_5IYBdhLWcg3wEOvq
    COMMIT
END

使用方法非常简单：

EXEC sp_drop_table_identity @tableName = 'some_very_big_table'

优点和限制：

它使用切换分区（适用于未分区的表）语句进行快速移动而无需完全复制数据。它还应用了一些适用条件。
它可以在不带标识的情况下进行即时表复制。这种解决方案我也单独发布过，并且对于像复合字段这样不太常见的结构可能需要调整（它满足我的需求）。
如果表包含在由CHECKOUPTION（sp、视图）绑定的对象中，则会防止进行切换（请参见代码中的最后一条评论）。可以将其额外脚本化以暂时删除此类绑定。我还没有这样做。

欢迎所有反馈。

- Hubbitus

0

在SQL Server上删除身份列的最有效方法（特别是对于大型数据库）是直接修改DDL元数据。在2005年之前的SQL Server上，可以使用以下方法完成：

sp_configure 'allow update', 1
go
reconfigure with override
go

update syscolumns set colstat = 0 --turn off bit 1 which indicates identity column
where id = object_id('table_name') and name = 'column_name'
go

exec sp_configure 'allow update', 0
go
reconfigure with override
go

SQL Server 2005+不支持reconfigure with override，但是当SQL Server实例以单用户模式启动时（使用-m标志启动db实例，例如“C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\sqlservr.exe -m”，请确保以管理员身份运行），您可以执行Ad Hoc查询，并使用专用管理控制台（从SQL Management Studio连接时使用ADMIN：前缀，例如ADMIN：MyDatabase）。列元数据存储在sys.sysschobjs内部表中（未显示DAC）：

use myDatabase

update sys.syscolpars set status = 1, idtval = null -- status=1 - primary key, idtval=null - remove identity data
where id = object_id('table_name') AND name = 'column_name'

关于这种方法的更多信息可以在这个博客上找到

- user3555784

不需要在单用户模式下启动实例并黑客系统表来完成此操作。可以使用“alter table switch”作为仅元数据操作来完成。https://dev59.com/2m025IYBdhLWcg3wT0Lq#6086661 - Martin Smith

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Daren Thomas · Accepted Answer

您可以尝试：

SET IDENTITY_INSERT #Tmp_MyTable ON 
-- ... do stuff
SET IDENTITY_INSERT #Tmp_MyTable OFF

这将允许您选择#Tmp_MyTable，即使它具有标识列。

但是这样做不会起作用：

-- Create empty temp table
SELECT *
INTO #Tmp_MyTable
FROM MyTable
WHERE 1=0
...
WHILE ...
BEGIN
    ...
    SET IDENTITY_INSERT #Tmp_MyTable ON 

    INSERT INTO #Tmp_MyTable
    SELECT TOP (@n) *
    FROM MyTable

    SET IDENTITY_INSERT #Tmp_MyTable OFF 
    ...    
END

这会导致错误：“当使用列列表并且IDENTITY_INSERT为ON时，表“＃Tmp”的标识列只能指定显式值。”

似乎没有其他方法，除非实际删除该列 - 但这将改变列的顺序，正如OP所提到的那样。一个不太好看的解决办法是：基于#Tmp_MyTable创建一个新表...

我建议您编写一个存储过程，根据表名（MyTable）创建一个临时表，该表具有相同的列（按顺序），但缺少标识属性。

您可以使用以下代码：

select t.name as tablename, typ.name as typename, c.*
from sys.columns c inner join
     sys.tables t on c.object_id = t.[object_id] inner join
     sys.types typ on c.system_type_id = typ.system_type_id
order by t.name, c.column_id

了解TSQL中反射工作原理的方法是进行循环，对于所需表中的列执行动态(alter)语句，这些语句手工编写并存储在字符串中，然后再进行评估。我相信您需要为生成的表编写这样的存储过程。

您能否为全世界发布这样的存储过程？在其他论坛中，这个问题好像也经常出现...