Cascading data insert

Question

I have these 3 tables:

CREATE TABLE tblPrimary(
Id INT IDENTITY(1,1)  NOT NULL,
SampleID VARCHAR(8)
PRIMARY KEY (Id)
)

CREATE TABLE tblSecondary(
PrimaryId INT NOT NULL,
SampleName VARCHAR(50) NULL
)

CREATE TABLE tblSample(
SampleId VARCHAR(8)  NOT NULL,
Name VARCHAR(50) NULL
PRIMARY KEY (SampleId)
)

Some sample data for tblSample

INSERT INTO tblSample VALUES ('A-1101', 'The CP 1014')
INSERT INTO tblSample VALUES ('A-1102', 'The NT 1014')
INSERT INTO tblSample VALUES ('A-1103', 'The LO 1014')
INSERT INTO tblSample VALUES ('A-1104', 'The AE 1014')
INSERT INTO tblSample VALUES ('A-1105', 'The PW 1014')
INSERT INTO tblSample VALUES ('A-1106', 'The QW 1014')

I'm currently inserting data from tblSample to tblPrimary with the following query:

INSERT INTO tblPrimary
SELECT s.SampleID FROM tblSample s
LEFT JOIN tblPrimary p on s.SampleId = p.SampleID
WHERE s.SampleId NOT IN (SELECT SampleID FROM tblPrimary)

Now I want to insert data into tblSecondary also, during the data insert into tblPrimary.

• The newly generated `tblPrimary.PrimaryId` will be inserted into the tblSecondary.PrimiaryId` column
• `tblSample.Name` will be inserted into the `tblSecondary.SampleName` column
• It will be a cascading data inserting process

What do I need to do after the above insert query for this to get done? I want the tblSecondary result to be as follows:

Answer 1

You will need a table variable and output clause for this something like....

DECLARE @NewIds (ID INT, SampleID varchar(8));


insert into tblPrimary(SampleID)
OUTPUT inserted.ID, inserted.SampleID INTO  @NewIds (ID,SampleID )
select s.SampleID 
from tblSample s
left join tblPrimary p on s.SampleId = p.SampleID
where s.SampleId not in (select SampleID from tblPrimary)


-- Now insert rows into tblSecondary table 

INSERT INTO tblSecondary(PrimaryId, SampleName )
SELECT n.ID  , S.Name
FROM tblSample s
INNER JOIN @NewIds n  ON s.SampleId = n.SampleID