Copy rows from the same table and update the ID column

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

INSERT INTO ProuctDefinition (ProdID, Definition, Desc)
SELECT
  xxx, Definition, Desc
FROM
  ProductDefinition
WHERE
  ProdID = yyy

The xxx is your new ProdID and the yyy is your old one. This also assumes that DefID is automagically populated on INSERT.

Answer 2

Can use MERGE on SQL Server 2008, has the advantage of using OUTPUT to return the DefID values, assuming they are auto-generated e.g.

MERGE INTO ProductDefinition
USING (
       SELECT 16, P1.Definition, P1.Description
         FROM ProductDefinition AS P1
        WHERE P1.ProdID = 15
      ) AS source (ProdID, Definition, Description)
ON 0 = 1
WHEN NOT MATCHED THEN
   INSERT (ProdID, Definition, Description)
   VALUES (ProdID, Definition, Description)
   OUTPUT inserted.DefID, inserted.ProdID, 
             inserted.Definition, inserted.Description;