Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Thursday, 31 March 2016

SQL Merge




Merge Statement combines the sequence of conditional INSERT and UPDATE commands in a single atomic statement, depending on the existence of a record. This operation is commonly known as Upsert functionality



Syntax

Merge into <Receiving_data_table>
Using <Base_data_table>
When Matched Then
Update Set <col1> = <value>,
<col2> = <value>
When Not Matched Then
Insert (col1, col2,….)
Values(val1, val2,….);
 


You can use merge command to perform insert and update in a single command.

Ex:

SQL> Merge into student1 s1
        Using (select *From student2) s2
        On(s1.no=s2.no)
        When matched then
        Update set marks = s2.marks
        When not matched then
        Insert (s1.no,s1.name,s1.marks)
        Values(s2.no,s2.name,s2.marks);

In the above the two tables are with the same structure but we can merge different structured tables also but the datatype of the columns should match.

Assume that student1 has columns like no,name,marks and student2 has columns like no,  name, hno, city.

SQL> Merge into student1 s1
        Using (select *From student2) s2
        On(s1.no=s2.no)
        When matched then
        Update set marks = s2.hno
        When not matched then
        Insert (s1.no,s1.name,s1.marks)
        Values(s2.no,s2.name,s2.hno);

Share this article :

3 comments:

  1. Hi Dayakar,


    Allow me to show my gratitude bloggers. You guys are like unicorns. Never seen but always spreading magic. Your content is yummy. So satisfied.


    I have one table
    Number Re_Number Text
    11 1 Aruna
    11 2 Aruna
    13 10 *
    15 2 /
    15 6 /
    17 1 Aruna
    18 1 &
    18 2 MSBI
    18 3 &
    19 11 %


    I need Duplicate records(Text) that also based on Number example above table
    Aruna is uplicate record but i need display only Number d 11(becoz..Number column rows should be mach 11,11 rows mached,
    17 and row not mached 17 row not required
    18 Number two & mached but MSBI is there this is also not required)
    out put should be like
    11 1 Aruna
    11 2 Aruna
    15 2 /
    15 6 /
    only display special characters rows not multiple Number Columns
    example out put
    13 10 *
    19 11 %



    But great job man, do keep posted with the new updates.


    Obrigado,
    Thanvi

    ReplyDelete
  2. Hi Dayakar,

    Such vivid info on the SQL Merge Flabbergasted! Thank you for making the read a smooth sail!

    I need to insert 10lakhs record using bulk collect. I got an error in say 96000th record how to solve it????? Using stored procedure and collections
    Kindly share the program if anyone knows.

    Great effort, I wish I saw it earlier. Would have saved my day :)

    Thanks a heaps,
    Irene Hynes

    ReplyDelete
  3. Hi There,

    I’ve often thought about this SQL Merge. Nice to have it laid out so clearly. Great eye opener.

    I'm trying to set up a reminder. It needs to remind users of a reassessment date that occurs every 90 days based upon the first contact (enrollment_date) with a potential client. But I only need to show the closest upcoming date. So, if a client's next closest assessment date is 12-12-15 it would show that date until 12-13-15 when it would be beginning to show 3-11-16. Is that possible?

    Appreciate your effort for making such useful blogs and helping the community.

    Obrigado,
    Preethi.

    ReplyDelete