Как удалить свойство IDENTITY столбца в SQL Server 2005

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

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

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

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

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

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

Ответ 2 · рейтинг 8 · Daren Thomas

Вы могли бы попробовать

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

(приводит к ошибке "Явное значение для столбца идентификаторов в таблице '#Tmp' может быть указано только при использовании списка столбцов и IDENTITY_INSERT ON.")

Кажется, что нет способа без фактического удаления столбца, но это изменит порядок столбцов, как упоминалось в 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. Я считаю, что вам придется перебирать столбцы для рассматриваемой таблицы и выполнять динамические (созданные вручную, сохраненные в строках, а затем оцениваемые) операторы изменения для сгенерированной таблицы.

Не могли бы вы опубликовать такую ​​хранимую процедуру для остального мира? Этот вопрос, кажется, часто возникает и на других форумах ...

Ответ 3 · рейтинг 1 · cmsjr

РЕДАКТИРОВАТЬ Переключение 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

Ответ 4 · Hubbitus

Я написал эту процедуру как компиляцию множества ответов на автоматическое и быстрое удаление идентификатора столбца:

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://stackoverflow.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://stackoverflow.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://stackoverflow.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://stackoverflow.com/questions/1540988/sql-2005-force-table-rename-that-has-dependencies
    COMMIT
END

Использовать довольно просто:

EXEC sp_drop_table_identity @tableName = 'some_very_big_table'

Преимущества и ограничения:

1. Он использует переключатель раздела (применимо к несекционированным таблицам тоже) оператор для быстрого перемещения без полного копирования данных. Также применяются некоторые условия применимости.
2. Он делает копию таблицы на лету без идентификации. Такое решение я также публикую отдельно, и ему также может потребоваться настройка на нетривиальные структуры, такие как составные поля (это покрывает мои потребности) .
3. Если таблица включена в объекты со схемой, привязанной к CHECKOUPTION (sp, views), она предотвращает переключение (см. Последний комментарий в коде). Для временного удаления такой привязки может быть дополнительно задан сценарий. Я этого еще не делал.

Любые отзывы приветствуются.

Ответ 5 · user3555784

Самый эффективный способ отбросить столбцы идентификаторов (особенно для больших баз данных) на SQL Server - напрямую изменить метаданные DDL, а на SQL Server старше 2005 года это можно сделать с помощью:

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+ не поддерживает перенастройку с переопределением, но вы можете выполнять специальные запросы, когда экземпляр SQL Server запущен в однопользовательском режиме (запуск экземпляра базы данных с помощью -m em >, например "C: \ Program Files \ Microsoft SQL Server \ MSSQL10.MSSQLSERVER \ MSSQL \ Binn \ sqlservr.exe -m", убедитесь, что вы запускаете его от имени администратора) с выделенной консолью администратора (из 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'

Подробнее об этом подходе в этом блоге