34,587
社区成员
发帖
与我相关
我的任务
分享
create table tb(id int,col varchar(10))
insert into tb select 5,'aaa' union all select 8,'ccc' union all select 17,'ccc'
go
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_tb
(
id int NOT NULL IDENTITY (1, 1),
col varchar(10) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_tb ON
GO
IF EXISTS(SELECT * FROM dbo.tb)
EXEC('INSERT INTO dbo.Tmp_tb (id, col)
SELECT id, col FROM dbo.tb WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_tb OFF
GO
DROP TABLE dbo.tb
GO
EXECUTE sp_rename N'dbo.Tmp_tb', N'tb', 'OBJECT'
GO
COMMIT
go
insert into tb select 'eee'
select * from tb
/*
id col
----------- ----------
5 aaa
8 ccc
17 ccc
18 eee
(4 行受影响)
*/
drop table tb
alter table tablename alter column columnname int identity
alert table tablename alert column columnname int identity