SQL Server 将列转换为标识列 [英] SQL Server convert column to an identity column
本文介绍了SQL Server 将列转换为标识列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我使用的是 SQL Server 2008.一个 int 列我用作主键而不是标识列(其值将自动增加 1).我想将此列转换为标识列.有什么解决办法吗?
I am using SQL Server 2008. One int column I used as primary key but not identity column (whose value will be increased by 1 automatically). I want to convert this column to identity column. Any solutions?
提前致谢,乔治
推荐答案
很遗憾,您无法将现有表上的字段更改为 IDENTITY
.
Unfortunately, you cannot change a field to IDENTITY
on an existing table.
你应该:
- 使用
IDENTITY
字段创建一个新表 - 为新表发出
SET IDENTITY_INSERT ON
- 将数据插入到新表中
- 为新表发出
SET IDENTITY_INSERT OFF
- 放下旧桌子
- 将新表重命名为旧名称.
您可以使用 SSMS
更改字段类型,它会在幕后为您完成所有这些.
You can use SSMS
to change the field type, it will do all this for you behind the scenes.
这是一个示例表:
CREATE TABLE plain (id INT NOT NULL PRIMARY KEY)
INSERT
INTO plain
VALUES (1000)
和SSMS
生成的脚本:
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_plain
(
id int NOT NULL IDENTITY (1, 1)
) ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_plain SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_plain ON
GO
IF EXISTS(SELECT * FROM dbo.plain)
EXEC('INSERT INTO dbo.Tmp_plain (id)
SELECT id FROM dbo.plain WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_plain OFF
GO
DROP TABLE dbo.plain
GO
EXECUTE sp_rename N'dbo.Tmp_plain', N'plain', 'OBJECT'
GO
ALTER TABLE dbo.plain ADD CONSTRAINT
PK__plain__3213E83F1A609306 PRIMARY KEY CLUSTERED
(
id
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
COMMIT
这篇关于SQL Server 将列转换为标识列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文