How can I convert this code to support MS SQL TVP bulk insert and update-CodePudding

Currently, I am inserting values into table 'table_name' one by one. I can expect both insert and update requests for this query but during these operations, I have to take care of a condition that no same barcode will be provided to an attendee in a meeting. Now I want to change it to bulk operation using TVP approach. Could someone please help me with this? Note - Composite key for this table is (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

CodePudding user response：

It looks like you could do this with 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)
;

To exclude rows which have an existing barcode, you could do the following

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)
;