CODEWITHSUNDEEP
sqlsql-server-2012
Hsu Siu Fai
Hsu Siu Fai

Reputation: 53

SQL Merge output both matched and unmatched results

I have two keys for data, pk is supposed to be generated from database when a row is inserted, while fk is a complex key which is given by another system. I would like to produce a pk key for each fk key.

CREATE TABLE test_target (
[pk] [INT] IDENTITY(1,1),
[fk] [varchar](20) NOT NULL)

And I can use merge to ensure that a new pk is produced whenever there is no corresponding fk exists in the table and I know I can output the newly created ids.

CREATE TABLE test_source (
[fk] [varchar](20) NOT NULL)

INSERT INTO test_source VALUES('abc123'),('def456'),('ghi789')

MERGE test_target WITH (SERIALIZABLE) AS T
USING test_source AS U
ON U.fk = T.fk
WHEN NOT MATCHED THEN
INSERT (fk) VALUES(U.fk)
OUTPUT inserted.pk, inserted.fk;

However, what I really want is all the pk associated with the fk in the test_source table. So I can get all by joining two tables.

SELECT test_target.* FROM test_target
INNER JOIN test_source ON test_target.fk = test_source.fk

But I feel like the associate pk is already found when in the case of MATCHED in the merge statement, so it is duplicated effort to do another search on the target table. My question is that is there a way to output the MATCHED pk in the same merge statement?

Upvotes: 5

Views: 4750

Answers (1)

Andrew
Andrew

Reputation: 27294

Yes there is - at first I thought I had to touch the row and update it in some form but I've realized we can just trick it. The output clause will output any row the statement touches, not just the rows you did not match on, so you can include a when matched clause - the problem is to make it a null op.

create table foo
(
    id int
    ,bar varchar(30)
)

insert into foo (id, bar) values (1,'test1');
insert into foo (id, bar) values (2,'test2');
insert into foo (id, bar) values (3,'test3');

declare @temp int;
merge foo as dest
using
(
    values (2, 'an updated value')
        , (4, 'a new value')
) as src(id, bar) on (dest.id = src.id)
when matched then
update set @temp=1
when not matched then
insert (id,bar)
values (src.id, src.bar)
output $action, src.id;

You can see in the when matched clause, I set a declared variable to 1. This is oddly enough to be considered for the output clause to pick it up for output. You can distinguish which operation (insert vs update) has occurred if you need with the $action in the output.

This gives the following results:

$action     id
UPDATE  2
INSERT  4

Performance wise I'd want to test how to operated at scale, or whether the variable assignment would cause a throttling effect

Upvotes: 8

Related Questions