我如何将此代码转换为支持MS SQL TVP批量插入和更新

Question

目前，我正在逐个插入值到表'table_name'。我可以期待这个查询的插入和更新请求，但是在这些操作期间，我必须处理不向会议中的与会者提供相同条形码的情况。现在我想把它改为使用TVP方法的批量操作。有人能帮我一下吗?注-该表的组合键为(account_id, meeting_id, barcode)

IF NOT EXISTS (SELECT 1
FROM   table_name a
WHERE  a.account_id = #{account_id}
AND a.meeting_id = #{meeting_id}
AND a.barcode = #{barcode})
BEGIN
IF NOT EXISTS (SELECT 1
FROM   table_name a
WHERE  a.attendee_id = #{attendee_id})
INSERT INTO table_name
(account_id,
meeting_id,
barcode,
attendee_id)
VALUES      ( #{account_id},
#{meeting_id},
#{barcode},
#{attendee_id} )
ELSE
UPDATE table_name
SET    barcode = #{barcode}
WHERE  attendee_id = #{attendee_id}
END 

Answer 1

看起来你可以用MERGE。

MERGE table_name t
USING @YourTVP s
ON t.account_id = s.account_id
AND t.meeting_id = s.meeting_id
AND t.attendee_id = s.attendee_id
WHEN MATCHED THEN
UPDATE SET
barcode = s.barcode
WHEN NOT MATCHED THEN
INSERT (  account_id,   meeting_id,   barcode,  attendee_id)
VALUES (s.account_id, s.meeting_id, s.barcode,s.attendee_id)
;

要排除具有现有条形码的行，可以执行以下操作

WITH source AS (
SELECT *
FROM @YourTVP s
WHERE NOT EXISTS (SELECT 1
FROM table_name t
WHERE t.account_id = s.account_id
AND t.meeting_id = s.meeting_id
AND t.barcode = s.barcode)
)
MERGE table_name t
USING source s
ON t.account_id = s.account_id
AND t.meeting_id = s.meeting_id
AND t.attendee_id = s.attendee_id
WHEN MATCHED THEN
UPDATE SET
barcode = s.barcode
WHEN NOT MATCHED THEN
INSERT (  account_id,   meeting_id,   barcode,  attendee_id)
VALUES (s.account_id, s.meeting_id, s.barcode,s.attendee_id)
;