如何以编程方式将非身份列更改为身份一？

发布于 2024-07-17 08:29:31 字数 209 浏览 2 评论 0原文

我有一个表，其列 ID 为标识一。接下来，我创建新的非身份列 new_ID 并使用 ID 列 + 1 中的值更新它。如下所示：

new_ID = ID + 1

接下来，我删除 ID 列并将 new_ID 重命名为“ID”。

以及如何在这个新列“ID”上设置身份？

我想以编程方式执行此操作！

原文

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

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

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

I would like to do this programmatically!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

念﹏祤嫣 2024-07-24 08:29:31

据我所知，您必须创建一个临时表，并将ID字段创建为IDENTITY，然后从原始表中复制所有数据。最后，删除原始表并重命名临时表。这是一个表（名为 TestTable）的示例，该表仅包含一个名为 ID（整数，非 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

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

回复收藏 0 原文

一个人的夜不怕黑 2024-07-24 08:29:31

看起来 SQL Mobile 支持更改列标识，但在 SQL Server 2005 上不喜欢 BOL 中的示例。

因此，您的选择是使用标识列创建一个新的临时表，然后打开标识插入：

Create Table Tmp_MyTable ( Id int identity....)

SET IDENTITY_INSERT dbo.Tmp_Category ON

INSERT Into Tmp_MyTable (...)
Select From MyTable ....

Drop Table myTable

EXECUTE sp_rename N'dbo.Tmp_MyTable', N'MyTable', 'OBJECT'

此外，您可以尝试首先将该列添加为标识列，然后打开标识插入。然后删除原始列。但我不确定这是否有效。

Looks like SQL Mobile supports altering a columns identity but on SQL Server 2005 didn't like the example from BOL.

So your options are to create a new temporary table with the identity column, then turn Identity Insert on:

Create Table Tmp_MyTable ( Id int identity....)

SET IDENTITY_INSERT dbo.Tmp_Category ON

INSERT Into Tmp_MyTable (...)
Select From MyTable ....

Drop Table myTable

EXECUTE sp_rename N'dbo.Tmp_MyTable', N'MyTable', 'OBJECT'

Additionally you can try and add the column as an identity column in the first place and then turn identity insert on. Then drop the original column. But I am not sure if this will work.

回复收藏 0 原文