更改现有表中的Identity列Sql Server [英] alter Identity column in existing table Sql Server
本文介绍了更改现有表中的Identity列Sql Server的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
PERSON表在这里有115条记录.
ID |名称
1 | aaa
2 | bbb
3 | ccc
.
.
.
115 | abv
我需要更改PERSON表以获取列ID身份.
如何更改...
就像这样.
Hi,
PERSON table have 115 records here.
id |name
1 |aaa
2 |bbb
3 |ccc
.
.
.
115|abv
i need to alter PERSON table for column ID identity.
how to change ...
like this way.
alter table [PERSON]
alter column ID bigint not null Identity(1,1)
但我收到此错误
关键字"Identity"附近的语法不正确.
问候,
Karthikeyan,
班加罗尔.
but i get this error
Incorrect syntax near the keyword ''Identity''.
Regards,
Karthikeyan,
Bangalore.
推荐答案
请根据您的要求修改此脚本,以便在现有表中添加标识列.
please modified this script as per your requirement to add identity column to you existing table.
/* To prevent any potential data loss issues, you should review this script in detail
before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_PERSON
(
ID int NOT NULL IDENTITY (1, 1),
NAME varchar(25) NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_PERSON SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_PERSON ON
GO
IF EXISTS(SELECT * FROM dbo.PERSON)
EXEC('INSERT INTO dbo.Tmp_PERSON (ID, NAME)
SELECT ID, NAME FROM dbo.PERSON WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_PERSON OFF
GO
DROP TABLE dbo.PERSON
GO
EXECUTE sp_rename N'dbo.Tmp_PERSON', N'PERSON', 'OBJECT'
GO
COMMIT
有关更多信息,请查看此链接
SQL Server添加删除身份 [ ^ ]
或
Sql Server添加删除身份2 [
for more information please review this link
SQL Server Add Remove Identity[^]
OR
Sql Server Add Remove Identity 2[^]
尝试一下:
DBCC CHECKIDENT(表名",RESEED,10);
Try this:
DBCC CHECKIDENT ("tablename", RESEED, 10);
这篇关于更改现有表中的Identity列Sql Server的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文