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

I want to be able to insert data from a table with an identity column into a temporary table in SQL Server 2005.

我希望能够将具有标识列的表中的数据插入到SQL Server 2005中的临时表中。

The TSQL looks something like:

TSQL看起来像:

-- 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

The above code created #Tmp_Table with an identity column, and the insert subsequently fails with an error "An explicit value for the identity column in table '#Tmp_MyTable' can only be specified when a column list is used and IDENTITY_INSERT is ON."

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

Is there a way in TSQL to drop the identity property of the column in the temporary table without listing all the columns explicitly? I specifically want to use "SELECT *" so that the code will continue to work if new columns are added to MyTable.

TSQL中是否有一种方法可以在临时表中删除列的标识属性而不显式列出所有列?我特别想使用“SELECT *”,以便在将新列添加到MyTable时代码将继续工作。

I believe dropping and recreating the column will change its position, making it impossible to use SELECT *.

我相信删除和重新创建列将改变它的位置,使得无法使用SELECT *。

Update:

I've tried using IDENTITY_INSERT as suggested in one response. It's not working - see the repro below. What am I doing wrong?

我已尝试在一个响应中建议使用IDENTITY_INSERT。它不起作用 - 见下面的repro。我究竟做错了什么?

-- 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

Note that the error message "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." - I specifically don't want to use a column list as explained above.

请注意,只有在使用列列表并且IDENTITY_INSERT为ON时,才能指定错误消息“表'#TmpMyTable'中标识列的显式值”。 - 我特别不想使用如上所述的列列表。

Update 2 Tried the suggestion from Mike but this gave the same error:

更新2尝试了迈克的建议,但这给出了同样的错误:

-- 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

As for why I want to do this: MyTable is a staging table which can contain a large number of rows to be merged into another table. I want to process the rows from the staging table, insert/update my main table, and delete them from the staging table in a loop that processes N rows per transaction. I realize there are other ways to achieve this.

至于为什么我要这样做:MyTable是一个临时表,可以包含大量要合并到另一个表的行。我想处理登台表中的行,插入/更新我的主表,并在每个事务处理N行的循环中从登台表中删除它们。我意识到还有其他方法可以实现这一目标。

Update 3

I couldn't get Mike's solution to work, however it suggested the following solution which does work: prefix with a non-identity column and drop the identity column:

我无法让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's suggestion to store only the keys in the temporary table is also a good one, though in this specific case there are reasons I prefer to have all columns in the temporary table.

Mike建议只在临时表中存储密钥也是一个很好的建议,尽管在这种特定情况下我有理由希望在临时表中包含所有列。

5 个解决方案

#1

IF you are just processing rows as you describe, wouldn't it be better to just select the top N primary key values into a temp table like:

如果您只是按照描述处理行,那么只需将前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

The keys should not change very often (I hope) but other columns can with no harm to doing it this way.

密钥不应该经常更改(我希望),但其他列可以这样做无害。

get the @@ROWCOUNT of the insert and you can do a easy loop on TempID where it will be from 1 to @@ROWCOUNT

得到插入的@@ ROWCOUNT,你可以在TempID上轻松循环,从1到@@ ROWCOUNT

and/or

just join #KeysToProcess to your MyKeys table and be on your way, with no need to duplicate all the data.

只需将#KeysToProcess加入您的MyKeys表即可,无需复制所有数据。

This runs fine on my SQL Server 2005, where MyTable.MyKey is an identity column.

这在我的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

EDIT
THIS WORKS, with no errors...

编辑这个工作,没有错误......

-- 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

however, what is your real problem? Why do you need to loop while inserting "*" into this temp table? You may be able to shift strategy and come up with a much better algorithm overall.

但是,你真正的问题是什么?为什么在将“*”插入此临时表时需要循环?您可以改变策略并提出更好的算法。

#2

You could try

你可以试试

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

This will allow you to select into #Tmp_MyTable even though it has an identity column.

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

But this will not work:

但这不起作用:

-- 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

(results in the error "An explicit value for the identity column in table '#Tmp' can only be specified when a column list is used and IDENTITY_INSERT is ON.")

(导致错误“只有在使用列列表且IDENTITY_INSERT为ON时,才能指定表'#Tmp'中标识列的显式值。”)

It seems there is no way without actually dropping the column - but that would change the order of columns as OP mentioned. Ugly hack: Create a new table based on #Tmp_MyTable ...

似乎没有办法没有实际删除列 - 但这将改变OP提到的列的顺序。丑陋的黑客:基于#Tmp_MyTable创建一个新表...

I suggest you write a stored procedure that creates a temporary table based on a table name (MyTable) with the same columns (in order), but with the identity property missing.

我建议您编写一个存储过程,该存储过程基于具有相同列(按顺序)的表名(MyTable)创建临时表,但缺少标识属性。

You could use following code:

您可以使用以下代码:

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

to get a glimpse on how reflection works in TSQL. I believe you will have to loop over the columns for the table in question and execute dynamic (hand-crafted, stored in strings and then evaluated) alter statements to the generated table.

了解反射在TSQL中的工作原理。我相信你将不得不循环遍历相关表的列,并执行动态(手工制作,存储在字符串中然后评估)alter语句到生成的表。

Would you mind posting such a stored procedure for the rest of the world? This question seems to come up quite a lot in other forums as well...

你介意为世界其他地方发布这样的存储过程吗?这个问题在其他论坛中似乎也出现了很多......

#3

EDIT Toggling IDENTITY_INSERT as suggested by Daren is certainly the more elegant approach, in my case I needed to eliminate the identity column so that I could reinsert selected data into the source table

编辑按照Daren的建议切换IDENTITY_INSERT肯定是更优雅的方法,在我的情况下我需要删除标识列,以便我可以将所选数据重新插入源表

The way that I addressed this was to create the temp table just as you do, explicitly drop the identity column, and then dynamically build the sql so that I have a column list that excludes the identity column (as in your case so the proc would still work if there were changes to the schema) and then execute the sql here's a sample

我解决这个问题的方法是像你一样创建临时表,显式删除标识列,然后动态构建sql,以便我有一个排除标识列的列列表(在你的情况下,所以proc会如果模式有变化,仍然可以工作)然后在这里执行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

#4

I have wrote this procedure as compilation of many answers to automatically and fast drop column identity:

我已经编写了这个程序,作为自动和快速删除列标识的许多答案的汇编:

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://*.com/questions/21547/in-sql-server-how-do-i-generate-a-create-table-statement-for-a-given-table
    -- 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://*.com/questions/3907879/sql-server-howto-get-foreign-key-reference-from-information-schema
                    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://*.com/questions/3907879/sql-server-howto-get-foreign-key-reference-from-information-schema
    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://*.com/questions/1540988/sql-2005-force-table-rename-that-has-dependencies
    COMMIT
END

Use is pretty simple:

使用非常简单:

EXEC sp_drop_table_identity @tableName = 'some_very_big_table'

Benefits and limitations:

好处和局限:

1. It uses switch partition (applicable to not partitioned tables too) statement for fast move without full data copy. It also apply some conditions for applicability.

它使用切换分区(也适用于非分区表)语句,以便在没有完整数据副本的情况下快速移动。它还适用于某些适用条件。

2. It make on the fly table copy without identity. Such solution I also post separately and it also may need tuning on not so trivial structures like compound fields (it cover my needs).

它使飞行表副本没有身份。这样的解决方案我也单独发布,它也可能需要调整不那么简单的结构,如复合字段(它满足我的需求)。

3. If table included in objects with schema bound by CHECKOUPTION (sp, views) it prevent do switching (see last comment in code). It may be additionally scripted to temporary drop such binding. I had not do that yet.

如果表包含在具有由CHECKOUPTION(sp,views)绑定的模式的对象中,则它会阻止切换(请参阅代码中的最后一条注释)。它可以另外编写为临时删除这种绑定。我还没有这样做。

All feedback welcome.

欢迎所有反馈。

#5

Most efficient way to drop identity columns (especially for large databases) on SQL Server is to modify DDL metadata directly, on SQL Server older than 2005 this can be done with:

在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+ doesn't support reconfigure with override, but you can execute Ad Hoc Queries when SQL Server instance is started in single-user mode (start db instance with -m flag, i.e. "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\sqlservr.exe -m", make sure to run as Administrator) with Dedicated Admin Console (from SQL Management Studio connect with ADMIN: prefix, i.e. ADMIN:MyDatabase). Column metdata is stored in sys.sysschobjs internal table (not shown without DAC):

SQL Server 2005+不支持使用override进行重新配置,但是当SQL Server实例以单用户模式启动时,您可以执行Ad Hoc查询(使用-m标志启动数据库实例,即“C:\ Program Files \ Microsoft SQL Server” \ MSSQL10.MSSQLSERVER \ MSSQL \ Binn \ sqlservr.exe -m“,确保以管理员身份运行),使用专用管理控制台(从SQL Management Studio连接ADMIN:前缀,即ADMIN:MyDatabase)。列metdata存储在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' 

More on this approach on this blog

更多关于此博客的方法