;WITH e1(n) AS
(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), -- 10
e2(n) AS (SELECT 1 FROM e1 CROSS JOIN e1 AS b), -- 10*10
e3(n) AS (SELECT 1 FROM e1 CROSS JOIN e2), -- 10*100
e4(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY n) FROM e3 )
SELECT e4.n
FROM e4
LEFT OUTER JOIN <your table> ON e4.n = <your table>.Field1
WHERE Field1 IS NULL
ORDER BY n;
You can increase the number table by adding either changing the e3(n) line so that you cross join e2 with e2 or by adding another line to the number table.
DECLARE @StartNo BIgInt = 1;
DECLARE @EndNo BigInt = 5000;
with
x as (
select Field1, row_number() over (order by Field1) rn
from MyTable t
where Field1>= @StartNo and Field1<= @EndNo
)
select x1.Field1+1 gap_start, x2.Field1-1 gap_end
from x x1 inner join x x2 on x1.rn=x2.rn-1
where x1.Field1!= x2.Field1-1
Msg 8114, Level 16, State 5, Line 4
Error converting data type varchar to bigint
DECLARE @StartNo BIgInt = 100;
DECLARE @EndNo BigInt = 200;
with
Files as (select 121 Field1 union select 122 union select 125 union select 132),
x as (
select Field1, row_number() over (order by Field1) rn
from Files t
where Field1>= @StartNo and Field1<= @EndNo
)
select x1.Field1+1 gap_start, x2.Field1-1 gap_end
from x x1 inner join x x2 on x1.rn=x2.rn-1
where x1.Field1!= x2.Field1-1
gap_start gap_end
123 124
126 131
DECLARE @StartNo BIgInt = 100;
DECLARE @EndNo BigInt = 200;
with
x as (
select IdNo, row_number() over (order by IdNo) rn
from MyTable t
where IdNo>= @StartNo and IdNo<= @EndNo
)
select x1.IdNo+1 gap_start, x2.IdNo-1 gap_end
from x x1 inner join x x2 on x1.rn=x2.rn-1
where x1.IdNo!= x2.IdNo-1
Field1 is a varchar(255)
DECLARE @StartNo BIgInt = 100;
DECLARE @EndNo BigInt = 200;
with
x as (
select Cast(Field1 as bigint) Field1, row_number() over (order by Field1) rn
from Files t
where Cast(Field1 as bigint)>= @StartNo and Cast(Field1 as bigint)<= @EndNo
)
select x1.Field1+1 gap_start, x2.Field1-1 gap_end
from x x1 inner join x x2 on x1.rn=x2.rn-1
where x1.Field1!= x2.Field1-1
DECLARE @StartNo BIgInt = 100;
DECLARE @EndNo BigInt = 200;
with
x as (
select Cast(Field1 as bigint) Field1, row_number() over (order by Field1) rn
from Files t
where Cast(Field1 as bigint)>= @StartNo and Cast(Field1 as bigint)<= @EndNo
)
select x1.Field1+1 gap_start, x2.Field1-1 gap_end
from x x1 inner join x x2 on x1.rn=x2.rn-1
where x1.Field1!= x2.Field1-1
DECLARE @StartNo BIgInt = 100;
DECLARE @EndNo BigInt = 200;
with
x as (
select Cast(Field1 as bigint) Field1, row_number() over (order by Field1) rn
from Files t
where isnumeric(Field1)=1 and Cast(Field1 as bigint)>= @StartNo and Cast(Field1 as bigint)<= @EndNo
)
select x1.Field1+1 gap_start, x2.Field1-1 gap_end
from x x1 inner join x x2 on x1.rn=x2.rn-1
where x1.Field1!= x2.Field1-1
create view n as (
select 0 n union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9
);
create view nx6 as (select n6.n*100000 + n5.n*10000 + n4.n*1000 + n3.n*100 + n2.n*10 + n1.n as n from n n1, n n2, n n3, n n4, n n5, n n6)
DECLARE @StartNo BIgInt = 100;
DECLARE @EndNo BigInt = 200;
with
Files as (select 121 Field1 union select 122 union select 125 union select 132),
x as (
select Field1, row_number() over (order by Field1) rn
from Files t
where Field1>= @StartNo and Field1<= @EndNo
)
select nx6.n, x1.Field1+1 gap_start, x2.Field1-1 gap_end
from x x1 inner join x x2 on x1.rn=x2.rn-1 inner join (select n from nx6 where n>=@StartNo and n<=@EndNo) nx6 on nx6.n > x1.Field1 and nx6.n < x2.Field1
where x1.Field1!= x2.Field1-1
order by nx6.n
n gap_start gap_end
123 123 124
124 123 124
126 126 131
127 126 131
128 126 131
129 126 131
130 126 131
131 126 131
If you are experiencing a similar issue, please ask a related question
Title | # Comments | Views | Activity |
---|---|---|---|
Access denied running PowerPivot -SQL Server 2014 on Windows Server 2012 | 10 | 30 | |
Setting Macro in Access to Automate Running an Append at a Certain Time | 2 | 22 | |
Sql Query | 6 | 66 | |
2 IIF's in Access query | 25 | 28 |
Join the community of 500,000 technology professionals and ask your questions.