目前，我正在將值一一插入表 '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
uj5u.com熱心網友回復：
看起來你可以用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)
;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/339858.html
標籤：sql sql-server jpa 米巴蒂斯