13,002,343 members (79,161 online)
Rate this:
See more:
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 19:33pm
Sandeep Mewara 29-Mar-13 5:32am

What do you mean 'blank' identity value?

Rate this:

## 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]

Example table design:
`CREATE TABLE MyTable (ID INT INDENTITY(1,1), Column2 VARCHAR(30), Column3 INT)`

[/EDIT]
v2
Rate this:

## Solution 1

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

Top Experts
Last 24hrsThis month
 OriginalGriff 320 ppolymorphe 170 F-ES Sitecore 155 RickZeeland 139 Bohdan Stupak 115
 OriginalGriff 4,927 ppolymorphe 2,527 RickZeeland 2,350 Kornfeld Eliyahu Peter 2,031 Richard Deeming 1,673