Copy rows from the same table and update the ID co

Question

I have the following table

I have inserted Product B to it and it gives me an ID of 15

Then I have the definition table which is as follows.

I want to select the ProductDefinition rows where ProdID = 14 and replicate the same and insert it for ProdID = 15 like the following

How to achieve this using SQL code?

Answer 1

If your id is not autoincrement or declared sequence and if u dont want to create a temporary table:

you can use:

INSERT INTO Tabel1 SELECT ((ROW_NUMBER( ) OVER ( ORDER BY ID  )) + (SELECT  MAX(id) FROM Table1)) ,column2,coulmn3,'NewValue' FROM Tabel1 Where somecolumn='your value`