MERGE - sql server 2008 onwards - insert or update of bulk data or single data in single query

--Synchronize the target table with
--refreshed data from source table
MERGE Products AS TARGET
USING UpdatedProducts AS SOURCE 
ON (TARGET.ProductID = SOURCE.ProductID) 

--When records are matched, update 
--the records if there is any change

WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName OR TARGET.Rate <> SOURCE.Rate THEN 

  UPDATE SET TARGET.ProductName = SOURCE.ProductName, 
  TARGET.Rate = SOURCE.Rate 

--When no records are matched, insert
--the incoming records from source
--table to target table

WHEN NOT MATCHED BY TARGET THEN 

  INSERT (ProductID, ProductName, Rate) 
  VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)

--When there is a row that exists in target table and
--same record does not exist in source table
--then delete this record from target table

WHEN NOT MATCHED BY SOURCE THEN 

  DELETE

--$action specifies a column of type nvarchar(10) 
--in the OUTPUT clause that returns one of three 
--values for each row: 'INSERT', 'UPDATE', or 'DELETE', 
--according to the action that was performed on that row

OUTPUT $action, deleted.*, inserted.*;

 
SELECT @@ROWCOUNT;
GO

-----------------------------------------------------------

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
WHEN NOT MATCHED BY TARGET THEN
    INSERT (CustomerID, ProductID, LastPurchaseDate)
    VALUES (Source.CustomerID, Source.ProductID, Source.PurchaseDate)
OUTPUT $action, Inserted.*, Deleted.*; 

----------------------------------------------------------

-- Create Events table 
CREATE TABLE dbo.Events( 
    EventId int PRIMARY KEY, 
    DeviceId int NOT NULL, 
    Value int NOT NULL, 
    Timestamp datetime2(7) NULL
)
GO
-- Create sp_InsertEvents stored procedure 
CREATE PROCEDURE dbo.sp_ImportEvents  
    @Events NVARCHAR(MAX)
AS  
BEGIN 
    MERGE INTO dbo.Events AS A 
    USING ( 
        SELECT *
        FROM OPENJSON(@Events) WITH (EventId int, DeviceId int, Value int, Timestamp datetime2(7))) B
       ON (A.EventId = B.EventId) 
    WHEN MATCHED THEN 
        UPDATE SET A.DeviceId = B.DeviceId, 
                   A.Value = B.Value, 
                   A.Timestamp = B.Timestamp 
    WHEN NOT MATCHED THEN 
        INSERT (EventId, DeviceId, Value, Timestamp)  
        VALUES(B.EventId, B.DeviceId, B.Value, B.Timestamp); 
END 
GO
Create Procedure [dbo].[Upsert_Orders2] 
    @IsNullInsert Bit = 0,
    @OrderID Int = Null,
    @Ticker Varchar(20) = Null,
    @Size Decimal(31,15) = Null,
    @Price Decimal(31,15) = Null,
    @OrderStatus Tinyint = Null
As

Declare @OrderStatusChange Table(Oldstatus int, NewStatus int)

Begin Transaction

Merge
  dbo.Orders As target
Using
  (Select @OrderID As OrderID) As source
On 
  (target.OrderID = source.OrderID)
When Matched Then
  Update Set
    Ticker = Case When @IsNullInsert = 0 Then IsNull(@Ticker, target.Ticker) Else @Ticker End,
    Size = Case When @IsNullInsert = 0 Then IsNull(@Size, target.Size) Else @Size End,
    Price = Case When @IsNullInsert = 0 Then IsNull(@Price, target.Price) Else @Price End,
    OrderStatus = Case When @IsNullInsert = 0 Then IsNull(@OrderStatus, target.OrderStatus) Else @OrderStatus End
When Not Matched Then
  Insert 
    (Ticker, Size, Price, OrderStatus)
  Values
    (@Ticker, @Size, @Price, @OrderStatus)
Output
  deleted.OrderStatus, inserted.OrderStatus into @OrderStatusChange;

If Exists (Select 'x' From @OrderStatusChange Where NewStatus < OldStatus)
  -- Evil History Changer!
  Rollback Transaction
Else
  Commit Transaction


SQL: Fastest way to insert new records where one doesn’t already exist

The Different Techniques
------------------------
Other than the LEFT JOIN, what are other ways to accomplish this? Here are a few common ways that will be benchmarked:

1. Insert Where Not Exists
2. MERGE
3. Insert Except
4. LEFT JOIN


 


  

  

1) Insert Where
  Not Exists

  

  

INSERT
  INTO #table1 (Id, guidd, TimeAdded,
  ExtraData)

SELECT Id, guidd,
  TimeAdded, ExtraData

FROM
  #table2

WHERE NOT EXISTS (Select Id, guidd From
  #table1 WHERE #table1.id = #table2.id)

 

  

  

2) Merge

  

  

MERGE
  #table1 as [Target]

USING  (select Id, guidd, TimeAdded, ExtraData from #table2) as
  [Source]

(id,
  guidd, TimeAdded, ExtraData)

    on [Target].id =[Source].id

WHEN
  NOT MATCHED THEN

    INSERT (id, guidd, TimeAdded, ExtraData)

    VALUES ([Source].id, [Source].guidd,
  [Source].TimeAdded, [Source].ExtraData);

 

  

  

3) Insert Except

  

  

INSERT
  INTO #table1 (id, guidd, TimeAdded,
  ExtraData)

SELECT id, guidd,
  TimeAdded, ExtraData from #table2

EXCEPT

SELECT id, guidd,
  TimeAdded, ExtraData from #table1

 

  

  

4) Left Join

  

  

INSERT
  INTO #table1 (id, guidd, TimeAdded,
  ExtraData)

SELECT #table2.id,
  #table2.guidd, #table2.TimeAdded, #table2.ExtraData

FROM
  #table2

LEFT JOIN #table1 on
  #table1.id = #table2.id

WHERE #table1.id is null

 

1. below 5,000,000 -- left join performance faster
2. above 5,000,000 -- insert except will faster