CREATE TABLE #tblTest(
Sno BIGINT,
Branch VARCHAR(100)
)
INSERT INTO #tblTest
VALUES (1,'ABC'),
(2,'ABC'),
(3,'ABC'),
(5,'ABC'),
(6,'ABC'),
(8,'ABC'),
(10,'ABC')
WITH MaxMin
AS
(
SELECT MAX(Sno) AS MaxVal, MIN(Sno) AS MinVal
FROM #tblTest
),
Ranges
AS
(
SELECT MinVal AS Sno FROM MaxMin
UNION ALL
SELECT Sno + 1
FROM Ranges
WHERE Sno < (SELECT MaxVal AS Sno FROM MaxMin)
)
SELECT Sno AS MissingNo, 'ABC' AS Branch
FROM Ranges
WHERE Sno NOT IN (SELECT Sno FROM #tblTest)
If you would give proper ans the solution would be better :(