if exists (select * from sysobjects where id = OBJECT_ID('[t_IDNotContinuous]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [t_IDNotContinuous] CREATE TABLE [t_IDNotContinuous] ( [ID] [int] IDENTITY (1, 1) NOT NULL, [ValuesString] [nchar] (10) NULL) SET IDENTITY_INSERT [t_IDNotContinuous] ON INSERT [t_IDNotContinuous] ([ID],[ValuesString]) VALUES ( 1,'test') INSERT [t_IDNotContinuous] ([ID],[ValuesString]) VALUES ( 2,'test') INSERT [t_IDNotContinuous] ([ID],[ValuesString]) VALUES ( 3,'test') INSERT [t_IDNotContinuous] ([ID],[ValuesString]) VALUES ( 5,'test') INSERT [t_IDNotContinuous] ([ID],[ValuesString]) VALUES ( 6,'test') INSERT [t_IDNotContinuous] ([ID],[ValuesString]) VALUES ( 7,'test') INSERT [t_IDNotContinuous] ([ID],[ValuesString]) VALUES ( 10,'test') SET IDENTITY_INSERT [t_IDNotContinuous] OFF select * from [t_IDNotContinuous] go select ID,new_ID into [t_IDNotContinuous_temp] from ( select ID,new_ID = ( select top 1 ID from [t_IDNotContinuous] where ID=(select min(ID) from [t_IDNotContinuous] where ID>a.ID) ) from [t_IDNotContinuous] as a ) as b select * from [t_IDNotContinuous_temp] go select id from [t_IDNotContinuous_temp] where ID <> new_ID - 1
where id = OBJECT_ID('[t_IDNotContinuous]')
and OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [t_IDNotContinuous] CREATE TABLE [t_IDNotContinuous] (
[ID] [int] IDENTITY (1, 1) NOT NULL,
[ValuesString] [nchar] (10) NULL) SET IDENTITY_INSERT [t_IDNotContinuous] ON INSERT [t_IDNotContinuous] ([ID],[ValuesString]) VALUES ( 1,'test')
INSERT [t_IDNotContinuous] ([ID],[ValuesString]) VALUES ( 2,'test')
INSERT [t_IDNotContinuous] ([ID],[ValuesString]) VALUES ( 3,'test')
INSERT [t_IDNotContinuous] ([ID],[ValuesString]) VALUES ( 5,'test')
INSERT [t_IDNotContinuous] ([ID],[ValuesString]) VALUES ( 6,'test')
INSERT [t_IDNotContinuous] ([ID],[ValuesString]) VALUES ( 7,'test')
INSERT [t_IDNotContinuous] ([ID],[ValuesString]) VALUES ( 10,'test') SET IDENTITY_INSERT [t_IDNotContinuous] OFF select * from [t_IDNotContinuous]
go
select ID,new_ID
into [t_IDNotContinuous_temp]
from (
select ID,new_ID = (
select top 1 ID from [t_IDNotContinuous]
where ID=(select min(ID) from [t_IDNotContinuous] where ID>a.ID)
)
from [t_IDNotContinuous] as a
) as b select * from [t_IDNotContinuous_temp]
go
select id
from [t_IDNotContinuous_temp]
where ID <> new_ID - 1