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.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment