CODEWITHSUNDEEP
sql-servert-sql
Hi Ten
Hi Ten

Reputation: 93

SQL Server: Can Merge Insert Conditionally or Update a Source Table?

I am using SQL Server 2008 and synchronizing data between two tables like this:

    MERGE into Stock as Target
    using StockHistory as Source on Target.SrNo = Source.SrNo
    When Matched then
            # Update record to target table
    When Not Matched then
            # Insert record to target table
    When Not Matched By Source Then
            # Update Source Table - Current Record

Question 1: I would like to insert record conditionally "When Not Matched Then" fire. for e.g. in case Source table column refSrNo_StockCompany is null then it should not insert in target.

Question 2: in case target table "Stock" does not matched with source "When Not Matched By Source Then" fire and it should update the source table --> IsSoldOut = 'Yes'.

Kindly suggest how I can achieve this.

Thanks

Upvotes: 2

Views: 2991

Answers (1)

Solomon Rutzky
Solomon Rutzky

Reputation: 48806

For Question 1: just add an AND condition to the WHEN NOT MATCHED clause:

WHEN NOT MATCHED AND Source.[refSrNo_StockCompany] IS NOT NULL THEN
    # Insert record to target table

For Question 2: it cannot be done directly as only the TARGET table can be modified. However, it can be done indirectly in a second step. The trick is to use the OUTPUT clause to capture into a temp table the records that were updated (and, rather than use $action to distinguish between INSERT and UPDATE, we grab from the deleted table which, by definition, will never be populated on INSERT operations) . Then you use that list as a JOIN when doing an UPDATE on the source table to filter out anything that was in that list. The records in the temp table are the records that do not need to be updated since they matched. Any records in the source table that are not in that temp table represent all of the records that would have matched on the WHEN NOT MATCHED BY SOURCE condition. Of course, records that were inserted via the WHEN NOT MATCHED clause will all show up as NULL records in the temp table (as there is nothing in the deleted table for an INSERT operation), and those can be easily filtered out of the UPDATE.

-- do this before the MERGE
CREATE TABLE #UpdatedRecords (SrNo INT);

BEGIN TRY
  BEGIN TRAN;

  MERGE ...
  -- add this to the end of the MERGE query
  OUTPUT DELETED.SrNo
  INTO  #UpdatedRecords (SrNo);

  -- optionally do this after the MERGE
  DELETE upd
  FROM   #UpdatedRecords upd
  WHERE  upd.SrNo IS NULL;

  -- do this after the MERGE
  UPDATE sh
  SET    sh.IsSoldOut = 'Yes'
  FROM StockHistory sh
  WHERE NOT EXISTS (
          SELECT upd.SrNo
          FROM   #UpdatedRecords upd
          WHERE  upd.SrNo = sh.SrNo
       );

  COMMIT TRAN;

END TRY
BEGIN CATCH
  ROLLBACK TRAN;
  DECLARE @Error NVARCHAR(4000) = ERROR_MESSAGE();
  RAISERROR(@Error, 16, 1);
END CATCH;

And if you want to ensure consistency of these two statements being a single operation as they would have been when using MERGE (had that worked), then wrap the MERGE and the UPDATE in a BEGIN TRAN and COMMIT TRAN.

Upvotes: 5

Related Questions