Wednesday, May 20, 2009

Auto Increment Of Variables In SQL SERVER

 
DECLARE @TestingSourceTable TABLE (Value INT NOT NULL)
DECLARE @TestingTable TABLE (ID INT NOT NULL)

INSERT
INTO @TestingSourceTable

SELECT 100 UNION ALL
SELECT 200 UNION ALL
SELECT 300 UNION ALL
SELECT 400 UNION ALL
SELECT 500 UNION ALL
SELECT 600

SELECT (
--This gives number 1,2.. for the result set
ROW_NUMBER() OVER(ORDER BY(SELECT 1)) +
--If our @TestingTable is null
--So we use ISNULL(MAX(ID),0)
(SELECT ISNULL(MAX(ID),0)FROM @TestingTable )
AS ID,
[Value]
FROM @TestingSourceTable

No comments: