Need help in Row_num increment in insert statement

Transact-SQL
1

Hi, I need help to increment the row number in insert statement,

CREATE TABLE dbo.TestTable
(TestCol1 int NOT NULL,
TestCol2 nchar(10) NULL
);
GO

CREATE CLUSTERED INDEX IX_TestTable_TestCol1
ON dbo.TestTable (TestCol1);
GO

declare @MaxID int

SELECT TOP 1 @MaxID = ISNULL(TestCol1, 0) FROM dbo.TestTable ORDER BY TestCol1 DESC

INSERT INTO dbo.TestTable
(TestCol1,TestCol2)
SELECT @MAXID + ROW_NUMBER() OVER (ORDER BY Col_Name) ----will this work
, col_name from source_table

thanks

2

Yes, but this would work better:


INSERT INTO dbo.TestTable
(TestCol1,TestCol2)
SELECT (SELECT MAX(TestCol1) FROM dbo.TestTable) + 
    ROW_NUMBER() OVER (ORDER BY Col_Name) 
, col_name 
FROM source_table
3

thanks, working fine