SQL Merge 输出匹配和不匹配的结果

Question

我有两个数据键，pk 应该是在插入一行时从数据库生成的，而 fk 是由另一个系统给出的复杂键.我想为每个 fk key 生成一个 pk key 。

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

并且我可以使用合并来确保在表中不存在相应的 fk 时生成新的 pk 并且我知道我可以输出新创建的 ID .

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;

但是，我真正想要的是所有与test_source 表中的fk 关联的pk。所以我可以通过连接两个表来获得所有内容。

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

但我觉得在合并语句中 MATCHED 的情况下，关联 pk 已经找到，因此在目标表上进行另一次搜索是重复的工作。我的问题是有没有办法在同一个合并语句中输出 MATCHED pk？

Answer 1

是的 - 起初我以为我必须触摸该行并以某种形式更新它，但我意识到我们可以欺骗它。 output 子句将输出语句接触的任何行，而不仅仅是您未匹配的行，因此您可以包含一个 when matched 子句 - 问题是使其成为空操作。

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;

您可以在 when matched 子句中看到，我将声明的变量设置为 1。这很奇怪，输出子句会考虑将其拾取用于输出。如果您需要在输出中使用 $action，您可以区分发生了哪个操作(插入与更新)。

结果如下:

$action     id
UPDATE  2
INSERT  4

在性能方面，我想测试如何大规模操作，或者变量赋值是否会导致节流效果