sql server - Cascading data insert -

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 tblsample

insert tblsample values ('a-1101', 'the cp 1014') insert tblsample values ('a-1102', 'the nt 1014') insert tblsample values ('a-1103', 'the lo 1014') insert tblsample values ('a-1104', 'the ae 1014') insert tblsample values ('a-1105', 'the pw 1014') insert tblsample values ('a-1106', 'the qw 1014') 

i'm inserting data tblsample tblprimary following query:

insert tblprimary select s.sampleid tblsample s left join tblprimary p on s.sampleid = p.sampleid s.sampleid not in (select sampleid tblprimary) 

now want insert data tblsecondary also, during data insert tblprimary.

• the newly generated `tblprimary.primaryid` inserted tblsecondary.primiaryid` column
• `tblsample.name` inserted `tblsecondary.samplename` column
• it cascading data inserting process

what need after above insert query done? want tblsecondary result follows:

you need table variable , output clause like....

declare @newids (id int, sampleid varchar(8));   insert tblprimary(sampleid) output inserted.id, inserted.sampleid  @newids (id,sampleid ) select s.sampleid  tblsample s left join tblprimary p on s.sampleid = p.sampleid s.sampleid not in (select sampleid tblprimary)   -- insert rows tblsecondary table   insert tblsecondary(primaryid, samplename ) select n.id  , s.name tblsample s inner join @newids n  on s.sampleid = n.sampleid