Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005 SQL
like
 
1
2
3
4
15
16
17
 

i want to retrieve 6,7,8,9,10,11,12,13,14
 
how i can find with value is missing
Posted 28-Mar-13 20:33pm
Comments
Sandeep Mewara at 29-Mar-13 5:32am
   
What do you mean 'blank' identity value?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Try this:
DECLARE @tmp TABLE (ID INT)
 
INSERT INTO @tmp (ID)
VALUES(1)
INSERT INTO @tmp (ID)
VALUES(2)
INSERT INTO @tmp (ID)
VALUES(3)
INSERT INTO @tmp (ID)
VALUES(4)
INSERT INTO @tmp (ID)
VALUES(15)
INSERT INTO @tmp (ID)
VALUES(16)
INSERT INTO @tmp (ID)
VALUES(17)
 
DECLARE @mId INT
 
SELECT @mId = MAX(ID)
FROM @tmp
 
;WITH NewIds AS
(
	SELECT 1 AS nID 
	UNION ALL
	SELECT nID + 1 
	FROM NewIds
	WHERE nID<@mId
)
SELECT ni.nID
FROM NewIds AS ni 
WHERE ni.nID NOT IN (SELECT ID AS nID FROM @tmp)
 
In above code CTE[^] were used.
 
result:
nID
5
6
7
8
9
10
11
12
13
14
 
[EDIT]
By The Way... Bad table design! Please, alter your table, but first read it: IDENITY[^]
 
Example table design:
CREATE TABLE MyTable (ID INT INDENTITY(1,1), Column2 VARCHAR(30), Column3 INT)
[/EDIT]
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

I think you need a numbers table. I found this with a search: numbers table[^]
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 7,903
1 Sergey Alexandrovich Kryukov 7,192
2 DamithSL 5,604
3 Manas Bhardwaj 4,986
4 Maciej Los 4,820


Advertise | Privacy | Mobile
Web02 | 2.8.1411023.1 | Last Updated 29 Mar 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100