sql - Inserting data into multiple tables at same time -

this trying do:

let's have 2 tables dbo.source & dbo.destination want copy records source destination if natural key (unique non clustered) not exist in destination. if insert successful, output values temporary buffer table. next want list records source did have match in destination, , copy these buffer table.

is there anyway can achieve buffer table not hold redundant data ?

this current logic:

step1: records source table natural key not match destination , insert destination insert these buffer table flag

merge dbo.destination dest using dbo.source src  on dest.name = src.name --natural key when not matched insert (xxx) values (xxx) output src.id, inserted.id, 'flaga' dbo.buffer; 

step2: records source table natural key matched destination insert these buffer flag

insert dbo.buffer select src.id, src.name, 'flagb' dbo.source src  inner join dbo.destination dest on src.name = dest.name 

with logic, getting redundant rows buffer, not track inserts intended. can critique sql based on trying do.

when run second query, matches inserted rows. should this:

insert dbo.buffer select src.id, src.name, 'flagb' dbo.source src     inner join dbo.destination dest on src.name = dest.name not exists (     select * dbo.buffer b b.xxx = 'flaga' , b.yyy = src.name ) 

or use when matched target, long suggested.