CODEWITHSUNDEEP
sqlsql-server
BaseBallBatBoy
BaseBallBatBoy

Reputation: 685

Merge Statement with two inserts?

Given is a simple MERGE statement. Where I Insert/Update records into traget table. Question: is it possible to also Insert those values in another table with a flag beeing 0 for insert and 1 for update? Eg. when not match do insert into target and another table, when matched do update target and insert into another table.

MERGE dbo.FactBuyingHabits AS Target
USING (SELECT CustomerID, ProductID, PurchaseDate FROM dbo.Purchases) AS Source
  ON (Target.ProductID = Source.ProductID AND Target.CustomerID = Source.CustomerID)
WHEN MATCHED THEN
  UPDATE SET Target.LastPurchaseDate = Source.PurchaseDate
  --and insert into test_tbl values (1, Source.ProductID, Source.CustomerID) --?
WHEN NOT MATCHED BY TARGET THEN
  INSERT (CustomerID, ProductID, LastPurchaseDate)
  VALUES (Source.CustomerID, Source.ProductID, Source.PurchaseDate)
  --and insert into test_tbl values (0, Source.ProductID, Source.CustomerID) --?

Upvotes: 1

Views: 1922

Answers (1)

Fredou
Fredou

Reputation: 20100

you should read about OUTPUT

ex (source);

DECLARE @MergeOutput1 table
(
  ActionType nvarchar(10),
  BookID int,
  OldBookTitle nvarchar(50),
  NewBookTitle nvarchar(50),
  ModifiedDate datetime
);

-- use MERGE statement to perform update on Book2

MERGE Books2 AS b2
USING Books AS b1
ON (b2.BookID = b1.BookID)
WHEN MATCHED
THEN UPDATE
SET b2.BookTitle = b1.BookTitle
OUTPUT
    $action,
    INSERTED.BookID,
    DELETED.BookTitle,
    INSERTED.BookTitle,
    INSERTED.ModifiedDate
  INTO @MergeOutput1;

Upvotes: 0

Related Questions