Merge Statement Help

  • Hi Guys,

    I am using Merge Statement. Here is my requirement, I don't want to Insert data if Client State is NY, but I want to update all data

    When Not Matched

    and State not in ('NY')

    THEN INSERT

    the problem is sometime data NY data is inserted and sometime don't. Is anyone can help, am i doing right or not. Any help would be great appreciate.

    Thank You.

  • Look around for a "how to post" article so you can learn how to help us help you. Can you give us a sample table(s), sample data, sample code you are trying and what you expect to happen with the data?

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Here is the sample data that I just create FYI its a Sample data. The requirement are I don't want to Insert Customer data if Customer State is NY and KT, but I want to Update Customer data if customer live in State NY and KT.. Its working fine (On sample data this Merge Statement is working fine)

    Create Table TblCustomer

    (

    ID INT IDENTITY(1,1),

    Fnamevarchar(20),

    Lnamevarchar(20),

    Cityvarchar(20),

    Statevarchar(20)

    )

    Create Table TblSource

    (

    IDINT IDENTITY(1,1),

    Fnamevarchar(20),

    Lnamevarchar(20),

    Cityvarchar(20),

    Statevarchar(20)

    )

    Insert TblSource

    values ('Smith','James','Abc','NY')

    Insert TblSource

    values ('Smith','James','Abc','NY')

    Insert TblSource

    values ('Smith','James','xy','CA')

    Insert TblSource

    values ('Smith','James','Chicago','KT')

    Insert TblSource

    values ('Smith','James','Abc','CA')

    --My Merge Statement

    --SELECT * FROM TblSource

    --SELECT * FROM TblCustomer

    Merge INTO TblCustomer C

    Using (

    Select * from TblSource) S

    ON (C.Fname = S.Fname and C.Lname = S.Lname)

    WHEN MATCHED THEN

    UPDATE SET C.FNAME = S.FNAME

    WHEN NOT MATCHED

    AND S.STATE NOT IN ('NY','kt')

    THEN INSERT

    (

    FNAME,

    LNAME,

    CITY,

    STATE

    )

    VALUES

    (

    S.FNAME,

    S.LNAME,

    S.CITY,

    S.STATE

    );

  • If it works on your sample but not when you run it on real data then you need to find out the characteristics of the rows that do NOT work as you expect. That will tell you why it isn't working like you expect. My guess is that the fname/lname match is not hitting like you think it should. Most likely cause is padding with spaces or not, or maybe leading space(s) or possibly some non-printable characters in the data. look there first.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Not sure what exactly you want. From the SQL statement you posted it will always insert data for NY and KT if it does not match Fname and Sname, but only update if the state is not NY or kt. Is that what you intended? Oh, btw, is your database case sensitive?

    Regards,

    Jan

    --------------------------------------------------------------------------
    A little knowledge is a dangerous thing (Alexander Pope)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply