CODEWITHSUNDEEP
sql-servert-sql
Be Kind To New Users
Be Kind To New Users

Reputation: 10063

MERGE: INSERT on MATCH

I am downloading data that will have duplicates with previously downloaded data.

I am successfully using a MERGE statement to throw away the duplicates based on transaction number. Supposedly that is sufficient, but I would like to monitor if the detail ever changes on a particular transaction.

To that end I added a when matched clause on the merge with an additional insert that identifies the record as a duplicate.

This logic should not trigger very often so I am not too concerned that this method (if it worked) would report the same duplicate multiple times.

When I prepare this code I get this error message:

An action of type 'INSERT' is not allowed in the 'WHEN MATCHED' clause of a MERGE statement.

Is there a way to get the duplicate record to insert into this table or another table using the MERGE statement?

I am open to other solutions, but I would really like to find a way to do this with the MERGE statement because it would impact my code the least.

MERGE INTO dbo.TransactionDetail as t
USING (SELECT @TransNr --bigint
             ,@Detail  -- [VARCHAR](50) NOT NULL
      ) as s
      ([TranNr]
      ,[Detail]
      )
   on t.TranNr = s.TranNr and t.CHANGED_RECORD = 0
 when not matched then
      INSERT (CHANGED_RECORD
         ,[TranNr]
             ,[Detail]
             )
      VALUES(0, s.TranNr, s.Detail)
/* Adding this does not allow statement to be prepared....
 when matched and s.Detail <> t.Detail
 then
      INSERT (CHANGED_RECORD
         ,[TranNr]
             ,[Detail]
             )
      VALUES(1, s.TranNr, s.Detail)
*/
;

Upvotes: 0

Views: 2928

Answers (1)

Razvan Socol
Razvan Socol

Reputation: 5684

You can use an INSERT statement, like this:

INSERT INTO dbo.TransactionDetail (CHANGED_RECORD,TranNr,Detail)
SELECT  CASE WHEN EXISTS (
            SELECT * FROM dbo.TransactionDetail 
            WHERE TranNr=@TransNr AND CHANGED_RECORD=0 AND Detail<>@Detail
        ) THEN 1 ELSE 0 END AS CHANGED_RECORD,
        @TransNr AS TranNr, @Detail AS Detail
WHERE NOT EXISTS (
    SELECT * FROM dbo.TransactionDetail 
    WHERE TranNr=@TransNr AND CHANGED_RECORD=0 AND Detail=@Detail
)

This will skip inserting if a row which has CHANGED_RECORD=0 has the same detail. However, if the same detail it's found in another row which has CHANGED_RECORD=1, a new duplicate would be inserted. To avoid that, remove the AND CHANGED_RECORD=0 condition from the WHERE NOT EXISTS subquery.

You may also want to create a unique filtered index, to ensure unicity for the rows which have CHANGED_RECORD=0:

CREATE UNIQUE INDEX IX_TransactionDetail_Filtered 
ON TransactionDetail (TranNr) /*INCLUDE (Detail)*/ WHERE CHANGED_RECORD=0

The INCLUDE (Detail) clause could also marginally improve performance of queries that are looking for the Detail of the rows which have CHANGED_RECORD=0 (at the expense of some additional disk space and a small performance penalty when updating the Detail column of existing rows).

Upvotes: 1

Related Questions