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.