SQL Query (Merge Statement) Help

  • Hi Guys,

    I need help. I want to use a MERGE statement. The source and target tables are pretty big so I really want to use MERGE Statement
    Here is the issue. I want to compare SOURCE VS Target table with some filters in the target table. I am not sure how and where I can
    use a filter. in MERGE Statement. Below are my query and sample data.

    DECLARE @Targettable TABLE
    (
      id                [int] IDENTITY(1,1) NOT NULL,
      city            VARCHAR(25),
      PhoneNumber  VARCHAR(15),
        state            VARCHAR(5),
      bid                INTEGER NULL
    )

    DECLARE @FilterTable TABLE
    (
      id                [int] IDENTITY(1,1) NOT NULL,
      Fname            VARCHAR(50)
    )

    INSERT INTO @Targettable
       ( city, PhoneNumber, state, bid )
    select 'San Pedro','7609876788', 'CA',1
    UNION
    select 'Los Angles','7609876767', 'CA',1
    UNION
    select 'New York','7609876908', 'NY',1
    UNION
    select 'Los Angles','7609876767', 'CA',2
    UNION
    select 'Los Am','920786777', 'CA',2

    INSERT INTO @FilterTable
       ( Fname )
    SELECT 'FULL_EXECUTION123.CSV'
    UNION
    SELECT 'EXIT_EXECUTION123.CSV'

    --SELECT * FROM @Targettable
    --SELECT * FROM @FilterTable

    --The final query that I want to use as my TARGET Table START
    --SELECT
    --        T.city
    --        ,T.PhoneNumber
    --        ,T.state
    --        FROM @Targettable T
    --        INNER JOIN @FilterTable F
    --        ON T.bid = F.id
    --        AND SUBSTRING(F.Fname,1,4) = 'FULL'
    --The final query that I want to use as my TARGET Table END

    --Source Table
    DECLARE @SourceTable TABLE
    (
      id                [int] IDENTITY(1,1) NOT NULL,
      city            VARCHAR(25),
      PhoneNumber          VARCHAR(15),
        state            VARCHAR(5)
    )

    INSERT INTO @SourceTable
       ( city, PhoneNumber, state )
    SELECT 'San Pedro','7609876788','CA'
    UNION
    SELECT 'Los Angles','7609876767','CA'
    UNION
    SELECT 'Palm Beach','8798887867','CA'
    UNION
    select 'Los Am','920786777', 'CA'

    --  SELECT * FROM @SourceTable

    --Merge STATEMENT
    MERGE INTO @Targettable AS T
    USING
        (
            SELECT
                    city
                    ,PhoneNumber
                    ,state
                    FROM @SourceTable
        ) AS S
        ON
            ( S.city = T.city
            AND S.PhoneNumber = T.PhoneNumber
            AND S.state = T.state
            )
    WHEN NOT MATCHED THEN
    INSERT    (city,PhoneNumber,state,bid)
    VALUES (S.city,S.PhoneNumber,S.state,7)
    ;

    SELECT * FROM @Targettable

    Here what I want at the end result

    id    city        PhoneNumber    state    bid
    1    Los Am        920786777    CA    2
    2    Los Angles    7609876767    CA    1
    3    Los Angles    7609876767    CA    2
    4    New York       7609876908    NY    1
    5    San Pedro    7609876788    CA    1
    6    Palm Beach    8798887867    CA    7
    7    Los Am        920786777    CA    7

    Please let me know if my question is not clear.
    Thank You.

  • If you want help, first you have to have something that has some shot at working.   You've described a filter table with only an Fname column, but are joining to an id column of some kind, that doesn't exist in your declared table.    Also, please use the IF Codes to surround query text with a bracketed code="SQL" and /code set of tags, as that will improve readability considerably.   Please explain in more detail...

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • The query looks to be working correctly to me.  You show the results you are expecting to have '7 Los Am 920786777 CA 7'.  But this record would not be inserted from the source since it exists in the target table.
    DECLARE @Targettable TABLE
    (
        id [int] IDENTITY(1,1) NOT NULL,
        city VARCHAR(25),
        PhoneNumber VARCHAR(15),
        state VARCHAR(5),
        bid INTEGER NULL
    )
    ;

    INSERT INTO @Targettable
    ( city, PhoneNumber, state, bid )
    select 'San Pedro','7609876788', 'CA',1
    UNION
    select 'Los Angles','7609876767', 'CA',1
    UNION
    select 'New York','7609876908', 'NY',1
    UNION
    select 'Los Angles','7609876767', 'CA',2
    UNION
    select 'Los Am','920786777', 'CA',2
    ;

    --Source Table
    DECLARE @SourceTable TABLE
    (
        id [int] IDENTITY(1,1) NOT NULL,
        city VARCHAR(25),
        PhoneNumber VARCHAR(15),
        state VARCHAR(5)
    )
    ;

    INSERT INTO @SourceTable
    ( city, PhoneNumber, state )
    SELECT 'San Pedro','7609876788','CA'
    UNION
    SELECT 'Los Angles','7609876767','CA'
    UNION
    SELECT 'Palm Beach','8798887867','CA'
    UNION
    select 'Los Am','920786777', 'CA'
    ;

    --Merge STATEMENT
    MERGE INTO @Targettable AS T
    USING (
            SELECT
                city
                ,PhoneNumber
                ,state
            FROM @SourceTable
        ) AS S
    ON ( S.city = T.city
        AND S.PhoneNumber = T.PhoneNumber
        AND S.state = T.state
        )
    WHEN NOT MATCHED THEN
    INSERT (city,PhoneNumber,state,bid)
    VALUES (S.city,S.PhoneNumber,S.state,7)
    ;

    SELECT *
    FROM @Targettable

    Results:
    id    city    PhoneNumber    state    bid
    1    Los Am    920786777    CA    2
    2    Los Angles    7609876767    CA    1
    3    Los Angles    7609876767    CA    2
    4    New York    7609876908    NY    1
    5    San Pedro    7609876788    CA    1
    6    Palm Beach    8798887867    CA    7

    -------------------------------------------------------------
    we travel not to escape life but for life not to escape us
    Don't fear failure, fear regret.

  • I removed everything for @FilterTable since it was not used or needed for the MERGE statement.

    -------------------------------------------------------------
    we travel not to escape life but for life not to escape us
    Don't fear failure, fear regret.

  • Thanks for your help and sorry for not providing a full Info. 
    First of all, I created a sample data and sample scenario. 

    I have three tables. i.e
    1) Source
    2) Target
    3) Filter
    I want to compare data from Source with Target table. I don't want to compare with all Target data. So I want to link target table to Filter table to Pull only where filename = 'FULL'. I know I can use
    I know I can use below SQL to accomplish what I want, but I am wondering how I can use Merge SQL.

    Insert into @Targettable (city,PhoneNumber,state,bid)
    select
                city,PhoneNumber,state,'7'
            from @SourceTable S
    where not EXISTS (SELECT
                            ID
                             @Targettable T 
                             INNER JOIN @FilterTable f on t.bid = f.id
                             and substring(f.filename,1,4) = 'FULL'
                        WHERE T.CITY = S.CITY
                        AND T.PhoneNumber = s.PhoneNumber
                        and t.state = s.state
                        )

  • Use a CTE to filter your target table.

    WITH Tgt AS (
        select T.*
        FROM @Targettable T
        INNER JOIN @FilterTable f on t.bid = f.id
        WHERE f.filename LIKE 'FULL%'
    )

    MERGE INTO Tgt
    USING (
       SELECT
        city
        ,PhoneNumber
        ,state
       FROM @SourceTable
      ) AS S
    ON ( S.city = T.city
      AND S.PhoneNumber = T.PhoneNumber
      AND S.state = T.state
      )
    WHEN NOT MATCHED THEN
    INSERT (city,PhoneNumber,state,bid)
    VALUES (S.city,S.PhoneNumber,S.state,7)
    ;

    Drew

    PS: I changed your SUBSTRING to a LIKE, because SUBSTRING is not SARGable, but LIKE is as long as the search pattern does not begin with a wildcard.

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Thanks Drew. It works.

  • Use a CTE to filter your target table.

    Is the CTE able to make use of an appropriate index in this case? I am guessing yes but just wondered.

    ----------------------------------------------------

  • MMartin1 - Friday, November 3, 2017 1:54 AM

    Use a CTE to filter your target table.

    Is the CTE able to make use of an appropriate index in this case? I am guessing yes but just wondered.

    Yes, CTE's are essentially temporary views.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Thanks!

    ----------------------------------------------------

Viewing 10 posts - 1 through 9 (of 9 total)

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