none
t sql merge RRS feed

  • Question

  • Hi,

    I have a requirement where i am currently using SSIS merge join tools to perform the below and i am facing performance issue where it takes 45min-1 hr to complete the job for just ~50,000 rows.I am trying to optimize it and someone suggested i use tsql merge for it.The requirement as below,but issue i face here is the "insert" is only allowed for NOT MATCHED whereas as per my requirement i want to insert rows WHEN MATCHED as well so that i can preserve the history.Can someone suggest how else i can achieve the below.

    MERGE A AS TARGET

    USING B AS SOURCE
    ON ([Uname]=[UserName] and [Licenses]=[License])

    WHEN MATCHED AND TARGET.[LicenseEnd_Date]<>NULL 
    THEN 
    INSERT ([UserName],[License],[LicenseStart_Date],[LicenseEnd_Date],[ExtractDate]) 
    VALUES(SOURCE.[Uname],SOURCE.[Licenses],SOURCE.[ModifiedDate],NULL,CONVERT(DATE,GETDATE()))

    WHEN NOT MATCHED BY TARGET
    THEN
    INSERT ([UserName],[License],[LicenseStart_Date],[LicenseEnd_Date],[ExtractDate]) 
    VALUES(SOURCE.[Uname],SOURCE.[Licenses],SOURCE.[ModifiedDate],NULL,CONVERT(DATE,GETDATE()))

    WHEN NOT MATCHED BY SOURCE
    THEN 
    UPDATE SET TARGET.[LicenseEnd_Date]='VAR_MOD_DATE' WHERE TARGET.[LicenseEnd_Date]=NULL

    I would like better performance compared to Merge in SSIS.Also,someone who suggested this TSQL Merge also said lookup would be slow too,so i am stuck and not sure what i can do now.

    Wednesday, September 18, 2019 3:00 PM

Answers

  • This should work:

    DECLARE @target TABLE ([UserName] varchar(100), [License] varchar(100), [LicenseStart_Date] date, [LicenseEnd_Date] date);
    DECLARE @src1 TABLE ([UserName] varchar(100), [License] varchar(100), [LicenseStart_Date] date, [LicenseEnd_Date] date);
    DECLARE @src2 TABLE ([UserName] varchar(100), [License] varchar(100), [LicenseStart_Date] date, [LicenseEnd_Date] date);
    DECLARE @filedate date;
    
    
    INSERT INTO @target ([UserName], [License], [LicenseStart_Date], [LicenseEnd_Date]) VALUES
    ('sjohn','salesforce','2019-08-12','2019-09-10');
    
    INSERT INTO @src1 ([UserName], [License], [LicenseStart_Date], [LicenseEnd_Date]) VALUES
    ('tseigal','oracle','2019-04-07',NULL);
    
    INSERT INTO @src2 ([UserName], [License], [LicenseStart_Date], [LicenseEnd_Date]) VALUES
    ('sjohn','salesforce','2019-08-12','2019-09-10'),
    ('tseigal','oracle','2019-04-07',NULL);
    
    SET @filedate = '2019-09-10';
    
    /* Merge data src1*/
    MERGE @target AS tar
    USING @src1 AS src
    ON (src.[UserName]=tar.[UserName] and src.[License]=tar.[License]) AND tar.[LicenseEnd_Date] IS NULL
    
    WHEN NOT MATCHED BY TARGET
    THEN
    INSERT ([UserName],[License],[LicenseStart_Date],[LicenseEnd_Date]) 
    VALUES(src.[UserName],src.[License],src.[LicenseStart_Date], NULL)
    
    WHEN NOT MATCHED BY SOURCE
    THEN 
    UPDATE SET [LicenseEnd_Date]=@filedate;
    
    
    SELECT *
    FROM @target
    ORDER BY UserName, License, LicenseStart_Date;
    
    
    SET @filedate = '2019-09-11';
    
    /* Merge data src2*/
    MERGE @target AS tar
    USING @src2 AS src
    ON (src.[UserName]=tar.[UserName] and src.[License]=tar.[License]) AND tar.[LicenseEnd_Date] IS NULL
    
    WHEN NOT MATCHED BY TARGET
    THEN
    INSERT ([UserName],[License],[LicenseStart_Date],[LicenseEnd_Date]) 
    VALUES(src.[UserName],src.[License],src.[LicenseStart_Date], NULL)
    
    WHEN NOT MATCHED BY SOURCE AND tar.LicenseEnd_Date IS NULL
    THEN 
    UPDATE SET [LicenseEnd_Date]=@filedate;
    
    
    SELECT *
    FROM @target
    ORDER BY UserName, License, LicenseStart_Date;
    

    Wednesday, September 18, 2019 6:48 PM
    Moderator

All replies

  • You should fix the syntax for checking null using either is null or is not null first.

    When not matched then insert  and when matched then Update.

    I don't know why you need to insert when there is a match.

    Please mark up a sample table with sample data with script so we can test syntax from there.

    Wednesday, September 18, 2019 3:24 PM
    Moderator
  • Insert on match is required because:

    1. If the license ended yesterday and again is reactivated,i have to record this change as a new row with null as license end date.This way i am preserving the history.
    Wednesday, September 18, 2019 4:40 PM
  • I think what you actually want is this:

    MERGE A AS TARGET
    USING B AS SOURCE
    ON ([Uname]=[UserName] and [Licenses]=[License]) AND TARGET.[LicenseEnd_Date] IS NULL
    
    WHEN NOT MATCHED BY TARGET
    THEN
    INSERT ([UserName],[License],[LicenseStart_Date],[LicenseEnd_Date],[ExtractDate]) 
    VALUES(SOURCE.[Uname],SOURCE.[Licenses],SOURCE.[ModifiedDate],NULL,CONVERT(DATE,GETDATE()))
    
    WHEN NOT MATCHED BY SOURCE
    THEN 
    UPDATE SET TARGET.[LicenseEnd_Date]='VAR_MOD_DATE' WHERE TARGET.[LicenseEnd_Date]=NULL 

    Wednesday, September 18, 2019 5:19 PM
    Moderator
  • You can write separate queries for your insert or update without using the merge syntax.

    But you need to write correctly for some sql queries in sections.

    Wednesday, September 18, 2019 5:20 PM
    Moderator
  • I will attach sample source and target data to better explain my need to insert in when matched.

    I have attached the source and target samples.I want to preserve the history like the one for sjohn.

    • When source does not bring in sjohn+salesforce ,i update the end date to the day of extract.Meaning ,the license was revoked.
    • When the license is reactivated, sjohn+salesforce appears back again in the source.In that case,there is key match between source and destination,then i check if end date <> null.IF both are true ,then i insert sjohn+salesforce with null as end date indicating the license is reactivated.


    • Edited by msdnpublic1234 Wednesday, September 18, 2019 8:17 PM small correction in source 12-Sep snapshot
    Wednesday, September 18, 2019 6:20 PM
  • This should work:

    DECLARE @target TABLE ([UserName] varchar(100), [License] varchar(100), [LicenseStart_Date] date, [LicenseEnd_Date] date);
    DECLARE @src1 TABLE ([UserName] varchar(100), [License] varchar(100), [LicenseStart_Date] date, [LicenseEnd_Date] date);
    DECLARE @src2 TABLE ([UserName] varchar(100), [License] varchar(100), [LicenseStart_Date] date, [LicenseEnd_Date] date);
    DECLARE @filedate date;
    
    
    INSERT INTO @target ([UserName], [License], [LicenseStart_Date], [LicenseEnd_Date]) VALUES
    ('sjohn','salesforce','2019-08-12','2019-09-10');
    
    INSERT INTO @src1 ([UserName], [License], [LicenseStart_Date], [LicenseEnd_Date]) VALUES
    ('tseigal','oracle','2019-04-07',NULL);
    
    INSERT INTO @src2 ([UserName], [License], [LicenseStart_Date], [LicenseEnd_Date]) VALUES
    ('sjohn','salesforce','2019-08-12','2019-09-10'),
    ('tseigal','oracle','2019-04-07',NULL);
    
    SET @filedate = '2019-09-10';
    
    /* Merge data src1*/
    MERGE @target AS tar
    USING @src1 AS src
    ON (src.[UserName]=tar.[UserName] and src.[License]=tar.[License]) AND tar.[LicenseEnd_Date] IS NULL
    
    WHEN NOT MATCHED BY TARGET
    THEN
    INSERT ([UserName],[License],[LicenseStart_Date],[LicenseEnd_Date]) 
    VALUES(src.[UserName],src.[License],src.[LicenseStart_Date], NULL)
    
    WHEN NOT MATCHED BY SOURCE
    THEN 
    UPDATE SET [LicenseEnd_Date]=@filedate;
    
    
    SELECT *
    FROM @target
    ORDER BY UserName, License, LicenseStart_Date;
    
    
    SET @filedate = '2019-09-11';
    
    /* Merge data src2*/
    MERGE @target AS tar
    USING @src2 AS src
    ON (src.[UserName]=tar.[UserName] and src.[License]=tar.[License]) AND tar.[LicenseEnd_Date] IS NULL
    
    WHEN NOT MATCHED BY TARGET
    THEN
    INSERT ([UserName],[License],[LicenseStart_Date],[LicenseEnd_Date]) 
    VALUES(src.[UserName],src.[License],src.[LicenseStart_Date], NULL)
    
    WHEN NOT MATCHED BY SOURCE AND tar.LicenseEnd_Date IS NULL
    THEN 
    UPDATE SET [LicenseEnd_Date]=@filedate;
    
    
    SELECT *
    FROM @target
    ORDER BY UserName, License, LicenseStart_Date;
    

    Wednesday, September 18, 2019 6:48 PM
    Moderator
  • Thanks Tom for your effort.However, i have only one source called Staging where i load daily snapshot.I use this daily snapshot as source and compare it against the destination table to be loaded.

    Each of the source snapshot is flat file received on current date.I parse the contents and store it in a temp table ,which i use as source.From the image i attached:

    10 Sep 2019 is a snapshot with file date as 10 Sep

    12 Sep 2019 is the snapshot with file date as 12 Sep

    Wednesday, September 18, 2019 7:12 PM
  • Hi msdnpublic1234,

    Just to complement Tom Phillips solution, there are 2 very important spots in the T-SQL MERGE construct:

    1. MERGE ... USING ... ON (...), the ON clause specifies a unique identifier for both source and target data sets.
    2. Both clauses WHEN NOT MATCHED BY TARGET AND ..., and WHEN NOT MATCHED BY SOURCE AND ... allow to specify additional logical conditions to cover special cases like yours.

    Wednesday, September 18, 2019 8:09 PM
  • Hi msdnpublic1234,

    If you use 'WHEN MATCHED AND TARGET.[LicenseEnd_Date]<>NULL THEN INSERT…' , you will get an error 'An action of type 'INSERT' is not allowed in the 'WHEN MATCHED' clause of a MERGE statement'.

     

    So you need to change merge condition . Do you have any questions about the above code? I think you can refer to it and change @src2 into Staging .

     

    If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, September 19, 2019 6:42 AM
  • Yes i am trying that and i removed the @src1. I am not clear about the way it works though.Can someone explain as to how all use cases are covered by this query:

    • Not matched by source
    • not matched by target
    • matched between source and target
    Thursday, September 19, 2019 1:02 PM
  • Hi msdnpublic1234,

    You can see how it works by using the following SQL:

    -- DDL and data population, start
    --Create a target table
    DECLARE @Products TABLE (
       ProductID INT PRIMARY KEY,
       ProductName VARCHAR(100),
       Rate MONEY
    );
    
    INSERT INTO @Products
    VALUES (1, 'Tea', 10.00),
       (2, 'Coffee', 20.00),
       (3, 'Muffin', 30.00),
       (4, 'Biscuit', 40.00);
    
    --Create source table
    DECLARE @UpdatedProducts TABLE (
       ProductID INT PRIMARY KEY,
       ProductName VARCHAR(100),
       Rate MONEY
    );
    
    INSERT INTO @UpdatedProducts
    VALUES   (1, 'Tea', 10.00),
       (2, 'Coffee', 25.00),
       (3, 'Muffin', 35.00),
       (5, 'Pizza', 60.00);
    -- DDL and data population, end
    
    --SELECT * FROM @Products;
    --SELECT * FROM @UpdatedProducts;
    
    
    --MERGE SQL statement
    --Synchronize the target table with
    --refreshed data from source table
    ;MERGE INTO @Products /* WITH (UpdLock, HoldLock) */ AS TARGET  
    USING @UpdatedProducts AS SOURCE 
    ON (TARGET.ProductID = SOURCE.ProductID) 
    -- When rows are matched, update the rows if there is any change
    -- but only if something needs to be updated!!!
    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 rows are matched, insert the incoming rows 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 row does not exist in source table
    --then delete this row 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.ProductID AS TargetProductID, 
       DELETED.ProductName AS TargetProductName, 
       DELETED.Rate AS TargetRate, 
       INSERTED.ProductID AS SourceProductID, 
       INSERTED.ProductName AS SourceProductName, 
       INSERTED.Rate AS SourceRate;
    -- INTO #log (a, dt, what);
    
    /*
    We can see the results:
    - Coffee rate was updated from 20.00 to 25.00, 
    - Muffin rate was updated from 30.00 to 35.00, 
    - Biscuit was deleted 
    - Pizza was inserted.
    */
    SELECT * FROM @Products;

    Thursday, September 19, 2019 1:16 PM
  • Thanks Yitzhak foe the example,I had already referred to it previously.I am clear about how Tom's solution (applied to my use cases) worked.I needed to understand pertaining to my use case.
    Thursday, September 19, 2019 2:09 PM
  • If you solve your issue, please mark.
    Friday, September 20, 2019 7:07 AM
  • @src1 represents the data for 10 Sept and @src2 represents 12 Sept.  Just replace @src1 and @src2 with STAGE.

    Friday, September 20, 2019 11:36 AM
    Moderator
  • Hi msdnpublic1234,

    Did you mean that you would like to delete these rows? In your original script , you use UPDATE and INSERT instead of DELETE. So in your result it will not delete rows. Please check it.

    For more information , please refer to https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-2017 It will provide  an example . If you have understood it, I think it might be easy according to your logic .

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, September 30, 2019 8:07 AM
  • Thanks for follow up.The solution by Tom works,i tested.
    Wednesday, October 2, 2019 3:40 PM