--不連續其實沒影響 DECLARE @T TABLE(ID INT IDENTITY,[NAME] VARCHAR(10)) INSERT @T SELECT 'A' INSERT @T SELECT 'B' DELETE @T WHERE ID=2 INSERT @T SELECT 'C' INSERT @T SELECT 'D' SELECT * FROM @T SELECT ID=(SELECT COUNT(*) FROM @T WHERE ID<=T.ID),[NAME] FROM @T T /*ID NAME ----------- ---------- 1 A 3 C 4 D(影響 3 個資料列)ID NAME ----------- ---------- 1 A 2 C 3 D */
自动编号的ID 是不插进去的。楼主为什么不取消自动编号呢。用MAX就OK了。
如果实在要加,可以这样SET IDENTITY_INSERT TB ONINSERT TB(ID,COL1,...) VALUES(3,VALUE1,...)SET IDENTITY_INSERT TB OFF
DECLARE @T TABLE(ID INT IDENTITY,[NAME] VARCHAR(10))
INSERT @T SELECT 'A'
INSERT @T SELECT 'B'
DELETE @T WHERE ID=2
INSERT @T SELECT 'C'
INSERT @T SELECT 'D'
SELECT * FROM @T
SELECT ID=(SELECT COUNT(*) FROM @T WHERE ID<=T.ID),[NAME] FROM @T T
/*ID NAME
----------- ----------
1 A
3 C
4 D(影響 3 個資料列)ID NAME
----------- ----------
1 A
2 C
3 D
*/