BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_Table_1
(
id int NOT NULL IDENTITY (889, 1),
name nchar(10) NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_Table_1 SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_Table_1 ON
GO
IF EXISTS(SELECT * FROM dbo.Table_1)
EXEC('INSERT INTO dbo.Tmp_Table_1 (id, name)
SELECT id, name FROM dbo.Table_1 WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_Table_1 OFF
GO
DROP TABLE dbo.Table_1
GO
EXECUTE sp_rename N'dbo.Tmp_Table_1', N'Table_1', 'OBJECT'
GO
ALTER TABLE dbo.Table_1 ADD CONSTRAINT
PK_Table_1 PRIMARY KEY CLUSTERED
(
id
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
COMMIT
1 回答
你应该可以做下面这样的事情 . Table_1是我希望更新的表,现在包括一个标识列,而该列只是一个简单的int之前 . 请注意,在创建tmp_Table_1时,它正在创建一个标识列,该标识列将标识种子设置为889,因为之前的最大int id是888.然后,脚本将获取现有表中的所有数据并插入到tmp表中,然后删除旧表并将tmp表重命名为Table_1 . 通过将标识种子设置为889,您插入的下一行数据将自动插入id为890 . 这有意义吗?