12,075,542 members (66,897 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 20: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 536 ProgramFOX 300 Dave Kreskowiak 285 CHill60 160 Richard MacCutchan 159
 Dave Kreskowiak 2,796 OriginalGriff 2,690 Richard MacCutchan 1,834 CPallini 1,527 CHill60 1,379