1
DROP TABLE #ID
CREATE TABLE #ID (ID INT)
INSERT INTO #ID (ID)
VALUES (24),(65),(77),(44)

DECLARE @ID int
SELECT @ID =  MAX(ID) from #ID

DROP TABLE #name
CREATE TABLE #NAME (Name char (20))
INSERT INTO #NAME (name)
VALUES ('Ben'),('Alex'),('Mark') 

DROP TABLE #abc
CREATE TABLE #ABC (ID INT, Name char(20))
INSERT INTO #ABC (ID,Name)
SELECT @ID,name
FROM #name

SELECT * FROM #ABC

I want the process to pick up the maximum ID from #ID and then add 1 for the next record. So, expected result should be:

ID  Name
77  Ben                 
78  Alex                
79  Mark

Please help me getting around this logic without using cursors. Can I use IDENTITY(@ID,1) in any way? Thanks

Adriano Carneiro
  • 57,693
  • 12
  • 90
  • 123
Bhupinder Singh
  • 1,061
  • 1
  • 11
  • 21

1 Answers1

3

Replace:

Select * from #ABC

For:

Select ROW_NUMBER() OVER (ORDER BY ID)  + ID - 1, Name from #ABC

Working fiddle

Prahalad Gaggar
  • 11,389
  • 16
  • 53
  • 71
Adriano Carneiro
  • 57,693
  • 12
  • 90
  • 123