Copying data with small changes in SQL Server-CodePudding

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.

CodePudding user response：

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;