Tuesday, February 13, 2007

Filling in gaps in incrementing INT column in SQL Server

Suppose you have a unique integer column i in a table T. i may contain gaps. i contains m rows.

Suppose you wish to insert n values into i starting at some value x0 and incrementing. Let x equal the first integer greater than or equal to x0 not found in i.

In general, this can be solved by performing the following:

SELECT TOP n * 2 IDENTITY(INT, x, 1) AS j
INTO #temp
FROM T T1
CROSS JOIN T T2
GO

INSERT INTO T (i)
SELECT TOP n j
FROM #temp
LEFT OUTER JOIN T
ON T.i = #temp.j
WHERE T.i IS NULL
GO

There should now be m + n rows in T.