日期:2014-05-19  浏览次数:20645 次

如何修改主键加自动递增
CREATE   TABLE   [HX_AttendanceNoDutyMan]   (
[WS_ANID]   [int]   NOT   NULL   ,
[WS_AttendanceNoDutyManID]   [int]   NULL   ,
CONSTRAINT   [PK_HX_AttendanceNoDutyMan]   PRIMARY   KEY     CLUSTERED  
(
[WS_ANID]
)     ON   [PRIMARY]  
)   ON   [PRIMARY]
GO
如何用命令修改主键WS_ANID加自动递增

------解决方案--------------------
CREATE TABLE dbo.Tmp_aaa
(
sid int NOT NULL,
aaa char(10) NOT NULL,
aid int NOT NULL IDENTITY (1, 1)
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_aaa ON
GO
IF EXISTS(SELECT * FROM dbo.aaa)
EXEC( 'INSERT INTO dbo.Tmp_aaa (sid, aaa, aid)
SELECT sid, aaa, aid FROM dbo.aaa TABLOCKX ')
GO
SET IDENTITY_INSERT dbo.Tmp_aaa OFF
GO
DROP TABLE dbo.aaa
GO
EXECUTE sp_rename N 'dbo.Tmp_aaa ', N 'aaa ', 'OBJECT '
------解决方案--------------------
CREATE TABLE temp_AttendanceNoDutyMan(
WS_ANID int identity NOT NULL primary key,
[WS_AttendanceNoDutyManID] [int] NULL ,
CONSTRAINT [PK_HX_AttendanceNoDutyMan] PRIMARY KEY CLUSTERED
(
[WS_ANID]
)
)

select *
into temp_AttendanceNoDutyMan
from HX_AttendanceNoDutyMan

drop table HX_AttendanceNoDutyMan

exec sp_rename 'temp_AttendanceNoDutyMan ', 'HX_AttendanceNoDutyMan '