locked
Getting error when using Merge Statement RRS feed

  • Question

  • Hello All,

    I have a code and I am trying to use Merge statement. 

     

    MERGE DBO.DASHBOARD as target

    using(select @cut_date) as source (effective_Date)

    when matched then 

     

      update bb

      set bb.cut_date = a.cut_date

      from dbo.dashboard bb

      inner join

       (select 

            @cut_date cut_date

           ,@invoice_gen invoice_gen

           ,@unclaimed_payment unclaimed_payment

           ,@payment_received payment_received

           ,@tdu_charge tdu_charge

           ,@coll_payment_received coll_payment_received

           ,@meter_active meter_active

           ,@meter_Cancelled meter_Cancelled

           ,@meter_churn meter_churn

           ,@meter_intransit meter_intransit

           ,ecp_tot_cte.tot_unstat

           ,ecp_tot_cte.active_unstat

           ,ecp_tot_cte.inactive_unstat

           ,ecp_tot_cte.tot_notdue

           ,ecp_tot_cte.active_notdue

           ,ecp_tot_cte.inactive_notdue

           ,ecp_tot_cte.tot_1_10

           ,ecp_tot_cte.active_1_10

           ,ecp_tot_cte.inactive_1_10

           ,ecp_tot_cte.tot_11_30

           ,ecp_tot_cte.active_11_30

           ,ecp_tot_cte.inactive_11_30

           ,ecp_tot_cte.tot_31_60

           ,ecp_tot_cte.act_31_60

           ,ecp_tot_cte.in_31_60

           ,ecp_tot_cte.tot_61_90

           ,ecp_tot_cte.act_61_90

           ,ecp_tot_cte.in_61_90

           ,ecp_tot_cte.tot_91_120

           ,ecp_tot_cte.act_91_120

           ,ecp_tot_cte.in_91_120

           ,ecp_tot_cte.tot_over_

           ,ecp_tot_cte.act_over_121

           ,ecp_tot_cte.in_over_121

           ,tot_cte.tot_unstat

           ,tot_cte.active_unstat

           ,tot_cte.inactive_unstat

           ,tot_cte.tot_notdue

           ,tot_cte.active_notdue

           ,tot_cte.inactive_notdue

           ,tot_cte.tot_1_10

           ,tot_cte.active_1_10

           ,tot_cte.inactive_1_10

           ,tot_cte.tot_11_30

           ,tot_cte.active_11_30

           ,tot_cte.inactive_11_30

           ,tot_cte.tot_31_60

           ,tot_cte.act_31_60

           ,tot_cte.in_31_60

           ,tot_cte.tot_61_90

           ,tot_cte.act_61_90

           ,tot_cte.in_61_90

           ,tot_cte.tot_91_120

           ,tot_cte.act_91_120

           ,tot_cte.in_91_120

           ,tot_cte.tot_over_

           ,tot_cte.act_over_121

           ,tot_cte.in_over_121

    FROM ecp_tot_cte 

       CROSS JOIN tot_cte ) a 

    On a.cut_date=bb.effective_date

     

    when not matched then

     

     

    INSERT INTO dbo.dashboard (

            [Effective_Date]

           ,[Invoice_gen]

           ,[unclaimed_payments]

           ,[Tot_Payments_received]

           ,[tdu_charge]

           ,[payments_received]  

           ,[meter_cnt_active] 

      ,[meter_cnt_cancelled]

           ,[meter_cnt_churn]

      ,[meter_cnt_intransit]

           ,[ag_tot_unstat]

           ,[ag_active_unstat]

           ,[ag_inactive_unstat]

           ,[ag_tot_notdue]

           ,[ag_active_notdue]

           ,[ag_inactive_notdue]

           ,[ag_tot_1_10]

           ,[ag_active_1_10]

           ,[ag_inactive_1_10]

           ,[ag_tot_11_30]

           ,[ag_active_11_30]

           ,[ag_inactive_11_30]

           ,[ag_tot_31_60]

           ,[ag_act_31_60]

           ,[ag_in_31_60]

           ,[ag_tot_61_90]

           ,[ag_act_61_90]

           ,[ag_in_61_90]

           ,[ag_tot_91_120]

           ,[ag_act_91_120]

           ,[ag_in_91_120]

           ,[ag_tot_over_121]

           ,[ag_act_over_121]

           ,[ag_in_over_121]

           ,[ecp_tot_unstat]

           ,[ecp_active_unstat]

           ,[ecp_inactive_unstat]

           ,[ecp_tot_notdue]

           ,[ecp_active_notdue]

           ,[ecp_inactive_notdue]

           ,[ecp_tot_1_10]

           ,[ecp_active_1_10]

           ,[ecp_inactive_1_10]

           ,[ecp_tot_11_30]

           ,[ecp_active_11_30]

           ,[ecp_inactive_11_30]

           ,[ecp_tot_31_60]

           ,[ecp_act_31_60]

           ,[ecp_in_31_60]

           ,[ecp_tot_61_90]

           ,[ecp_act_61_90]

           ,[ecp_in_61_90]

           ,[ecp_tot_91_120]

           ,[ecp_act_91_120]

           ,[ecp_in_91_120]

           ,[ecp_tot_over_121]

           ,[ecp_act_over_121]

           ,[ecp_in_over_121]

     

     

     

    select 

            @cut_date

           ,@invoice_gen

           ,@unclaimed_payment

           ,@payment_received

           ,@tdu_charge

           ,@coll_payment_received

           ,@meter_active

           ,@meter_Cancelled

           ,@meter_churn

           ,@meter_intransit

           ,tot_cte.tot_unstat

           ,tot_cte.active_unstat

           ,tot_cte.inactive_unstat

           ,tot_cte.tot_notdue

           ,tot_cte.active_notdue

           ,tot_cte.inactive_notdue

           ,tot_cte.tot_1_10

           ,tot_cte.active_1_10

           ,tot_cte.inactive_1_10

           ,tot_cte.tot_11_30

           ,tot_cte.active_11_30

           ,tot_cte.inactive_11_30

           ,tot_cte.tot_31_60

           ,tot_cte.act_31_60

           ,tot_cte.in_31_60

           ,tot_cte.tot_61_90

           ,tot_cte.act_61_90

           ,tot_cte.in_61_90

           ,tot_cte.tot_91_120

           ,tot_cte.act_91_120

           ,tot_cte.in_91_120

           ,tot_cte.tot_over_

           ,tot_cte.act_over_121

           ,tot_cte.in_over_121

          , ecp_tot_cte.tot_unstat

           ,ecp_tot_cte.active_unstat

           ,ecp_tot_cte.inactive_unstat

           ,ecp_tot_cte.tot_notdue

           ,ecp_tot_cte.active_notdue

           ,ecp_tot_cte.inactive_notdue

           ,ecp_tot_cte.tot_1_10

           ,ecp_tot_cte.active_1_10

           ,ecp_tot_cte.inactive_1_10

           ,ecp_tot_cte.tot_11_30

           ,ecp_tot_cte.active_11_30

           ,ecp_tot_cte.inactive_11_30

           ,ecp_tot_cte.tot_31_60

           ,ecp_tot_cte.act_31_60

           ,ecp_tot_cte.in_31_60

           ,ecp_tot_cte.tot_61_90

           ,ecp_tot_cte.act_61_90

           ,ecp_tot_cte.in_61_90

           ,ecp_tot_cte.tot_91_120

           ,ecp_tot_cte.act_91_120

           ,ecp_tot_cte.in_91_120

           ,ecp_tot_cte.tot_over_

           ,ecp_tot_cte.act_over_121

           ,ecp_tot_cte.in_over_121

    FROM ecp_tot_cte 

       CROSS JOIN tot_cte  

     

    ecp_tot_cte and tot_cte are both CTE's which has some calculations. They are defined above the merge statement. Here I am trying to do is when the cut_date is matched update the records in the table. When not matched insert the records. Here I am getting lot of errors near the MERGE Statement. Please assist.

    Thanks

    Friday, January 6, 2012 8:59 PM

Answers

  • Yes, you need all fields in the Source table and you need to specify correct ON condition (usually based on the primary key in both tables).
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by Stephanie Lv Saturday, January 14, 2012 7:58 AM
    Friday, January 6, 2012 10:01 PM
  • As Naomi mentioned, you should have on condition. Take a look at this example.
    ALTER PROCEDURE dbo.InsertUnitMeasure
        @UnitMeasureCode nchar(3),
        @Name nvarchar(25)
    AS 
    BEGIN
        SET NOCOUNT ON;
    
        MERGE Production.UnitMeasure AS target
        USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name)
        ON (target.UnitMeasureCode = source.UnitMeasureCode)
        WHEN MATCHED THEN 
            UPDATE SET Name = source.Name
    	WHEN NOT MATCHED THEN	
    	    INSERT (UnitMeasureCode, Name)
    	    VALUES (source.UnitMeasureCode, source.Name)
    	    OUTPUT deleted.*, $action, inserted.* INTO #MyTempTable;
    END;
    GO
    • Marked as answer by Stephanie Lv Saturday, January 14, 2012 7:58 AM
    Saturday, January 7, 2012 1:02 AM
  • MERGE is a new feature that provides an efficient way to perform multiple DML operations. In previous versions of SQL Server, we had to write separate statements to INSERT, UPDATE, or DELETE data based on certain conditions, but now, using MERGE statement we can include the logic of such data modifications in one statement that even checks when the data is matched then just update it and when unmatched then insert it.

    One of the most important advantage of MERGE statement is all the data is read and processed only once. In previous versions three different statement has to be written to process three different activity (INSERT, UPDATE or DELETE), however using MERGE statement all update activity can be done in one pass of database table. This is quite an improvement in performance of database query.

    Syntax of MERGE statement is as following:
    MERGE
    [ TOP ( expression ) [ PERCENT ] ]
    [ INTO ] target_table [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias]
    USING <table_source>
    ON <merge_search_condition>
    [ WHEN MATCHED [ AND <clause_search_condition> ]
    THEN <merge_matched> ]
    [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]
    THEN <merge_not_matched> ]
    [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]
    THEN <merge_matched> ]
    [ <output_clause> ]
    [ OPTION ( <query_hint> [ ,...n ] ) ]
    ;
    Example:

    Let’s create Student Details and StudentTotalMarks and inserted some records.

    Student Details:

     

    <strong><strong style="padding:0px; margin:0px">USE AdventureWorks
    GO
    CREATE TABLE StudentDetails
    (
    StudentID INTEGER PRIMARY KEY,
    StudentName VARCHAR(15)
    )
    GO
    INSERT INTO StudentDetails
    VALUES(1,'SMITH')
    INSERT INTO StudentDetails
    VALUES(2,'ALLEN')
    INSERT INTO StudentDetails
    VALUES(3,'JONES')
    INSERT INTO StudentDetails
    VALUES(4,'MARTIN')
    INSERT INTO StudentDetails
    VALUES(5,'JAMES')
    GO</strong></strong>
    
     
    StudentTotalMarks:

     

     

    <strong style="padding:0px; margin:0px"><strong style="padding:0px; margin:0px">CREATE TABLE StudentTotalMarks
    (
    StudentID INTEGER REFERENCES StudentDetails,
    StudentMarks INTEGER
    )
    GO
    INSERT INTO StudentTotalMarks
    VALUES(1,230)
    INSERT INTO StudentTotalMarks
    VALUES(2,255)
    INSERT INTO StudentTotalMarks
    VALUES(3,200)
    GO</strong></strong>
    

    In our example we will consider three main conditions while we merge this two tables.

    1. Delete the records whose marks are more than 250.
    2. Update marks and add 25 to each as internals if records exist.
    3. Insert the records if record does not exists.

    Now we will write MERGE process for tables created earlier. We will make sure that we will have our three conditions discussed above are satisfied.

    MERGE StudentTotalMarks AS stm
    USING (SELECT StudentID,StudentName FROM StudentDetails) AS sd
    ON stm.StudentID = sd.StudentID
    WHEN MATCHED AND stm.StudentMarks > 250 THEN DELETE
    WHEN MATCHED THEN UPDATE SET stm.StudentMarks = stm.StudentMarks + 25
    WHEN NOT MATCHED THEN
    INSERT(StudentID,StudentMarks)
    VALUES(sd.StudentID,25);
    GO
    


     

    • Marked as answer by Stephanie Lv Saturday, January 14, 2012 7:58 AM
    Saturday, January 7, 2012 4:49 PM

All replies

  • You're missing ON clause in your merge statement. If you're only selecting one date field in your source, then how do you plan to match the data sets at all?

    This blog post 

    SQL Advent 2011 Day 10: Upsert by using the Merge statement

    shows the sample of the MERGE - scroll to the middle of the blog.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Edited by Naomi N Friday, January 6, 2012 9:49 PM
    Friday, January 6, 2012 9:48 PM
  • Do I need to select all the fields in the table?...In the ON clause I think I need to select the date.
    Friday, January 6, 2012 9:58 PM
  • Yes, you need all fields in the Source table and you need to specify correct ON condition (usually based on the primary key in both tables).
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by Stephanie Lv Saturday, January 14, 2012 7:58 AM
    Friday, January 6, 2012 10:01 PM
  • I never used merge statement. I am not getting  where to add merge in my sp.
    Friday, January 6, 2012 10:14 PM
  • As Naomi mentioned, you should have on condition. Take a look at this example.
    ALTER PROCEDURE dbo.InsertUnitMeasure
        @UnitMeasureCode nchar(3),
        @Name nvarchar(25)
    AS 
    BEGIN
        SET NOCOUNT ON;
    
        MERGE Production.UnitMeasure AS target
        USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name)
        ON (target.UnitMeasureCode = source.UnitMeasureCode)
        WHEN MATCHED THEN 
            UPDATE SET Name = source.Name
    	WHEN NOT MATCHED THEN	
    	    INSERT (UnitMeasureCode, Name)
    	    VALUES (source.UnitMeasureCode, source.Name)
    	    OUTPUT deleted.*, $action, inserted.* INTO #MyTempTable;
    END;
    GO
    • Marked as answer by Stephanie Lv Saturday, January 14, 2012 7:58 AM
    Saturday, January 7, 2012 1:02 AM
  • MERGE is a new feature that provides an efficient way to perform multiple DML operations. In previous versions of SQL Server, we had to write separate statements to INSERT, UPDATE, or DELETE data based on certain conditions, but now, using MERGE statement we can include the logic of such data modifications in one statement that even checks when the data is matched then just update it and when unmatched then insert it.

    One of the most important advantage of MERGE statement is all the data is read and processed only once. In previous versions three different statement has to be written to process three different activity (INSERT, UPDATE or DELETE), however using MERGE statement all update activity can be done in one pass of database table. This is quite an improvement in performance of database query.

    Syntax of MERGE statement is as following:
    MERGE
    [ TOP ( expression ) [ PERCENT ] ]
    [ INTO ] target_table [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias]
    USING <table_source>
    ON <merge_search_condition>
    [ WHEN MATCHED [ AND <clause_search_condition> ]
    THEN <merge_matched> ]
    [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]
    THEN <merge_not_matched> ]
    [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]
    THEN <merge_matched> ]
    [ <output_clause> ]
    [ OPTION ( <query_hint> [ ,...n ] ) ]
    ;
    Example:

    Let’s create Student Details and StudentTotalMarks and inserted some records.

    Student Details:

     

    <strong><strong style="padding:0px; margin:0px">USE AdventureWorks
    GO
    CREATE TABLE StudentDetails
    (
    StudentID INTEGER PRIMARY KEY,
    StudentName VARCHAR(15)
    )
    GO
    INSERT INTO StudentDetails
    VALUES(1,'SMITH')
    INSERT INTO StudentDetails
    VALUES(2,'ALLEN')
    INSERT INTO StudentDetails
    VALUES(3,'JONES')
    INSERT INTO StudentDetails
    VALUES(4,'MARTIN')
    INSERT INTO StudentDetails
    VALUES(5,'JAMES')
    GO</strong></strong>
    
     
    StudentTotalMarks:

     

     

    <strong style="padding:0px; margin:0px"><strong style="padding:0px; margin:0px">CREATE TABLE StudentTotalMarks
    (
    StudentID INTEGER REFERENCES StudentDetails,
    StudentMarks INTEGER
    )
    GO
    INSERT INTO StudentTotalMarks
    VALUES(1,230)
    INSERT INTO StudentTotalMarks
    VALUES(2,255)
    INSERT INTO StudentTotalMarks
    VALUES(3,200)
    GO</strong></strong>
    

    In our example we will consider three main conditions while we merge this two tables.

    1. Delete the records whose marks are more than 250.
    2. Update marks and add 25 to each as internals if records exist.
    3. Insert the records if record does not exists.

    Now we will write MERGE process for tables created earlier. We will make sure that we will have our three conditions discussed above are satisfied.

    MERGE StudentTotalMarks AS stm
    USING (SELECT StudentID,StudentName FROM StudentDetails) AS sd
    ON stm.StudentID = sd.StudentID
    WHEN MATCHED AND stm.StudentMarks > 250 THEN DELETE
    WHEN MATCHED THEN UPDATE SET stm.StudentMarks = stm.StudentMarks + 25
    WHEN NOT MATCHED THEN
    INSERT(StudentID,StudentMarks)
    VALUES(sd.StudentID,25);
    GO
    


     

    • Marked as answer by Stephanie Lv Saturday, January 14, 2012 7:58 AM
    Saturday, January 7, 2012 4:49 PM
  • If you're using the logic - if this is a new record, do insert, otherwise do update, then this is the perfect place for the MERGE. Look closer into your SP and apply merge when you're doing both INSERT and UPDATE conditionally. The link I pointed you to is a good start. There are a few more examples in that site about MERGE.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Sunday, January 8, 2012 1:05 AM
  • Hi Sri,

    Please add an ON clause in your query.

    you'll get solved your query.

    Cheers,

    John

    Sunday, January 8, 2012 7:53 AM