如何以编程方式将非身份列更改为一个身份? [英] How to change programmatically non-identity column to identity one?

问题描述

我有一个表，其列ID为标识1.接下来，我创建一个新的非标识列new_ID并使用ID列+ 1中的值对其进行更新.像这样:

I have a table with column ID that is identity one. Next I create new non-identity column new_ID and update it with values from ID column + 1. Like this:

new_ID = ID + 1

接下来，我删除ID列，并将new_ID重命名为"ID".

Next I drop ID column and rename new_ID to name 'ID'.

以及如何在此新列"ID"上设置身份"?

And how to set Identity on this new column 'ID'?

我想以编程方式做到这一点！

I would like to do this programmatically!

推荐答案

据我所知，您必须使用ID字段创建为IDENTITY的临时表，然后复制原始表中的所有数据.最后，删除原始表并重命名临时表.这是一个表(名为 TestTable )的示例，该表仅包含一个称为ID(整数，非IDENTITY)的字段:

As far as I know, you have to create a temporary table with the ID field created as IDENTITY, then copy all the data from the original table. Finally, you drop the original table and rename the temporary one. This is an example with a table (named TestTable) that contains only one field, called ID (integer, non IDENTITY):

BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_TestTable
    (
    ID int NOT NULL IDENTITY (1, 1)
    )  ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_TestTable ON
GO
IF EXISTS(SELECT * FROM dbo.TestTable)
     EXEC('INSERT INTO dbo.Tmp_TestTable (ID)
        SELECT ID FROM dbo.TestTable WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_TestTable OFF
GO
DROP TABLE dbo.TestTable
GO
EXECUTE sp_rename N'dbo.Tmp_TestTable', N'TestTable', 'OBJECT' 
GO
COMMIT

这篇关于如何以编程方式将非身份列更改为一个身份?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！