Copying data with small changes in SQL Server

Question

I have a unique issue. I have 2 tables that I must copy data into while changing the control group from 1 to 2. For every record in Controls, there is a record in Controls_Data to provide other information. Here's an example:

CREATE TABLE Controls (
PK_Controls INT IDENTITY,
PK_ControlGroup INT,
Description VARCHAR(50))

CREATE TABLE Controls_Data(
PK_Controls_Data INT IDENTITY,
PK_Controls INT,
PK_ControlGroup INT,
Data VARCHAR(50))

The first table I have no issue with. Copying the data can be accomplished simply by saying:

INSERT INTO Controls(PK_ControlGroup, Description)
Select 2, Description From Controls Where PK_Controls = 1

However copying the data from the second table becomes an issue. I now have to copy the data from Controls_Data while filling the PK_Controls column with the Primary keys of the new rows I created from the previous statement. I can't find a way to do this because the data being copied uses the old primary keys from the controls in group 1. Any solution would be greatly appreciated.

Answer 1

CREATE TABLE Controls (
PK_Controls INT IDENTITY,
PK_ControlGroup INT,
Description VARCHAR(50))

CREATE TABLE Controls_Data(
PK_Controls_Data INT IDENTITY,
PK_Controls INT,
PK_ControlGroup INT,
Data VARCHAR(50))

insert into Controls values(1, 'desc 1');
insert into Controls values(1, 'desc 2');

insert into Controls_Data (PK_Controls, PK_controlGroup )
select PK_Controls, PK_controlGroup 
from 
    Controls where PK_ControlGroup = 1
--then insert 1 controls as 2 control group also into Controls_Data
insert into Controls (PK_controlGroup, [Description])
output
    INSERTED.PK_Controls,
    INSERTED.PK_ControlGroup
into Controls_Data(PK_Controls, PK_ControlGroup)
select 2 , [Description]
from 
    Controls where PK_ControlGroup = 1;