Inserting data into multiple tables at same time

Question

This is what I am trying to do:

Let's say I have two tables dbo.source & dbo.destination I want to copy all records from source to destination IF a certain natural key (unique non clustered) does not exist in the destination. If the insert is successful, then output some values to a temporary buffer table. Next I want to list all the records from the source which DID have a match in the destination, and copy these as well to the buffer table.

Is there anyway I can achieve this so that the buffer table does not hold redundant data ?

This is my current logic:

Step1: Get records from the source table where the natural key does not match the destination and insert into destination Insert these into buffer table with flag

MERGE INTO dbo.Destination dest USING dbo.Source AS src 
ON dest.Name = src.Name --Natural Key
WHEN NOT MATCHED THEN
INSERT (xxx) VALUES (xxx)
OUTPUT src.ID, Inserted.ID, 'flagA'
INTO dbo.Buffer;

Step2: Get records from the source table where the natural key matched the destination Insert these into buffer with a flag

Insert INTO dbo.Buffer
Select src.ID, src.Name, 'flagB'
FROM dbo.Source src 
inner join dbo.Destination dest
on src.Name = dest.Name

With this logic, I am getting redundant rows into my buffer, which do not exactly track the inserts as intended. Can anyone critique my sql based on what I am trying to do.

Answer 1

You can try it some like it, the dislike of this technique is that you always update one field. Maybe, you need to adapt my example to your needs.

DECLARE @Source table (id int identity , myValue varchar(5))
DECLARE @Destination table (id int identity  , myValue varchar(5))
DECLARE @Buffer table (sourceId int , InsertId varchar(5),flag varchar(5))

insert @Source (myValue) values ( 'a') ,( 'e'),( 'i'),( 'o'),( 'u')
insert @Destination (myValue) values ('a') ,('b'),('c')

;merge @Destination t 
using @Source S 
on
t.myValue = s.myValue
when not matched then insert (myValue) values (s.myValue)
when matched then update set myValue = t.myValue
output s.id, inserted.id, case $action when 'INSERT' then 'flagA' else 'flagB' end into @Buffer;


select * from @Destination
select * from @Buffer

Result

Destination table

id          myValue
----------- -------
1           a
2           b
3           c
4           e
5           i
6           o
7           u

Buffer table

sourceId    InsertId flag
----------- -------- -----
2           4        flagA
3           5        flagA
4           6        flagA
5           7        flagA
1           1        flagB

use this output

output s.id, case $action when 'INSERT' then Cast(inserted.id as varchar(5)) else inserted.myValue end , case $action when 'INSERT' then 'flagA' else 'flagB' end into @Buffer;

for

sourceId    InsertId flag
----------- -------- -----
2           4        flagA
3           5        flagA
4           6        flagA
5           7        flagA
1           a        flagB

Answer 2

When you run your second query, it also matches the just inserted rows. You should do something like this:

Insert INTO dbo.Buffer
Select src.ID, src.name, 'flagB'
FROM dbo.Source src
    inner join dbo.Destination dest on src.Name = dest.Name
where not exists (
    select * from dbo.Buffer b where b.xxx = 'flagA' and b.yyy = src.name
)

Or just use when matched by target, as LONG suggested.