CODEWITHSUNDEEP
sql-servert-sql
Hakka-4
Hakka-4

Reputation: 87

How to add a sequential number to a row in an Insert Statement for a Master Table?

Table A has historical records. Table B has 100 new records.

I need to append the data from Table B to Table A. Table A was built with a Row Number(using the Row_Number function) The last record entry had a row number of 100, how do I append my new data into that table and continue the row record to 101 and so on?

Upvotes: 0

Views: 664

Answers (1)

gotqn
gotqn

Reputation: 43646

Maybe something like the following:

BEGIN TRAN

DECLARE @MaxValue INT;

SELECT @MaxValue = MAX([record_id])
FROM TableA;

INSERT INTO TableA
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) -- or order by something else you need
       +
       @MaxValue
       ,* 
FROM TableB 

COMMIT TRAN

Upvotes: 1

Related Questions