You are here
Home > Sql Server > Merge statement in sql

Merge statement in sql

MERGE STATEMENT

  • We can merge data from a source table into a target table using merge statement. The practical use of merge statement in data warehousing and in OLTP (Online Transaction Processing).
  • Syntax:
  • Merge into <target_tablename> as T

    Using <source_tablename> as S

    ON <merge_condition>

    When matched then

    <update or delete action>

    When not matched by target then

    <action must be insert>

    When not matched by source then

    <update or delete action>;

Explanation:

  • MERGE into <target_tablename>: It is used to define the target table for merge statement. Here, we can also give alias name to the target table.
  • USING <source_tablename>: It defines source table name for the operation.
  • ON<merge condition>: In this clause, we specify a predicate for row matching between source table and target table.
  • WHEN MATCHED then: It defines an action to take when a source row is matched with a target row. Target row is exit, so we cannot use insert action. Update and delete actions are allowed.
  • WHEN NOT MATCHED BY target then: It defines which action to take when a source row is not matched by target row. We have to perform insert action because a row is not exist in target.
  • WHEN NOT MATCHED BY source then: It defines which action to take when a target row is not matched by source row. We can perform either an update or delete action because target row is exist.

 

  • Example:
  •  

    Table: target                                                 

    Id

    Name City
    1 Anil

    Anand

    2

    Jiya

    Bakrol

     

     

    Table: source

    Id

    Name City
    1 Atul

    Bakrol

    2

    Nita

    Anand

     

     

     

  • Merge target as T
  • using source as S

    on T.Id= S.Id

    when matched then

    update set T.Name= S.Name

    when not matched by target then

    insert (Id, Name) values (S.Id, S.Name)

    when not matched by source then

    delete;

  • Output:
  • Here, when we execute query it gives output like 2 rows affected.
  • Now, execute below query to see the output.
  • Select * from target;
  • Output:
  • Id

    Name

    City

    1

    Atul Anand
    2 Nita

    Bakrol

     

     

    Keep visiting my site ask2tech.com for upcoming top technical article

    Learn more about DBA at TechNet Consultancy

Leave a Reply

Top