mergining between source and target tables

milan i 81 Reputation points
2021-06-24T23:03:53.36+00:00

Good Morning,

Can you please help with below code.

FINALTable is the target table that needs appends/updates from #SourceTable(which is a daily refresh from feed).

here the MID, MName, MDOB is compbined data key / composite key so if the combination of three columns values exist in Target then if there is any other column values between source and target then need to update Target with source values

(
when the target table column having value and source is null or empty then no need to update on the columns (if record exists)
to optimize, update only the records, when there is changes (this could be flexible)
)

if the composite key not exist then need to insert whole record to the Target.

if there is no difference between source and taget record then ignore the record without any insertion/update in the target table.

--Target Table sample

CREATE TABLE #FINALTable (MID varchar(20), MName Varchar(50), MDOB DATE, Addr Varchar(50), City Varchar(25), ZIP Varchar(10), Phone Varchar(10))

INSERT INTO #FINALTable values ('101','AAA','9/18/1993','123 Victor BLVD', 'Kansas', '08909','2431231210')

INSERT INTO #FINALTable values ('102','BBB','12/18/1994','123 Victor Park', 'Kansas', '77834','2431231211')

INSERT INTO #FINALTable values ('103','CCC','7/10/1995','1893 SuLv RD', 'Kansas', '34534','2431231234')

INSERT INTO #FINALTable values ('104','DDD','07/09/1996','3 PARK', 'Kansas', '44444','2431231214')

INSERT INTO #FINALTable values ('105','EEE','7/08/1997','999 Merol DR', 'Kansas', '42424','2431231233')

INSERT INTO #FINALTable values ('106','FFF','7/10/1998','8 MOTT AVE', 'Kansas', '99234','8431231234')

--Source Table sample below incomments each record explained what it suppose to Do.

CREATE TABLE #SourceTable (MID varchar(20), MName Varchar(50), MDOB DATE, Addr Varchar(50), City Varchar(25), ZIP Varchar(10), Phone Varchar(10))

INSERT INTO #SourceTable values ('101','AAA','9/18/1993','123 Victor BLVD', 'Kansas', '08909','2431231210')

--- MID 101(the compkey MID, NAME, MDOB) exist in Target and no other column values changed in src vs target so no action need to take..

INSERT INTO #SourceTable values ('102','BBBJJJ','12/18/1994','123 Victor Park', 'Kansas', '77834','2431231211')

---MID 102 the Composite key (MID, NAME, MDOB) is changed for name column, the comp key is not exist in target, so new record need to be inserted, without updating 102

INSERT INTO #SourceTable values ('103','CCC','7/10/1995','1893 SuLv RD', 'NewYork', '34534','2431231234')

-- MID 103 the City name changed so master record need to update for 103 with Newyork value

INSERT INTO #SourceTable values ('104','DDD','07/09/1996','100 Manhatten PARK', 'Kansas', '44444','2222224444')

--MID 104 compkey exist in target ('104','DDD','07/09/1996'), phone no change and address change so need to update to target

INSERT INTO #SourceTable values ('111','EEE','7/08/1997','999 Merol DR', 'Kansas', '42424','2431231233')

---MID 111 ('111','EEE','7/08/1997') is not exist in the Target table so need to insert into target

INSERT INTO #SourceTable values ('106','XXX','7/10/1998','8 MOTT AVE', 'Kansas', '99234','8431231234')

--MID 106 composit key column changed which is not exist in Target ('106','XXX','7/10/1998') so need to insert as a new record

Please help

Thanks in advance

ASiti

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,544 questions
0 comments No comments
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-06-28T06:24:41.38+00:00

    Hi @milan i ,

    Based on Erland's query and update it as below.

    MERGE #FINALTable AS tgt    
    USING #SourceTable AS src ON tgt.MID   = src.MID   
                        AND tgt.MName = src.MName   
                        AND tgt.MDOB  = src.MDOB    
    WHEN MATCHED AND NOT EXISTS (SELECT src.Addr, src.City, src.ZIP, src.Phone  
                            INTERSECT  
                            SELECT tgt.Addr, tgt.City, tgt.ZIP, tgt.Phone) THEN  
    UPDATE  
    SET    tgt.Addr  = isnull(src.Addr,  tgt.Addr),  
        tgt.City  = isnull(src.City,  tgt.City),  
        tgt.ZIP   = isnull(Case when Isnumeric(left(src.zip,5)) = 1 and len(src.zip)>4 then left(src.zip,5) ELSE SRC.ZIP END,tgt.ZIP),  
        tgt.Phone = isnull(src.Phone, tgt.Phone)  
    WHEN NOT MATCHED THEN    
    INSERT (MID, MName, MDOB, Addr, City, ZIP, Phone)    
    VALUES (src.MID,src.MName, src.MDOB, src.Addr, src.City, src.ZIP, src.Phone);  
      
    select * from #FINALTable  
    

    If above is not working, please provide updated sample data with different zips in source table and expected output.

    Best regards,
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


2 additional answers

Sort by: Most helpful
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-06-25T02:19:45.043+00:00

    Hi @milan i ,

    Welcome to Microsoft Q&A!

    You could have a try with MERGE statement as below:

    MERGE #FINALTable AS tgt    
    USING #SourceTable AS src   
    ON (tgt.MID=SRC.MID AND tgt.MName=SRC.MName AND tgt.MDOB=SRC.MDOB)    
    WHEN MATCHED THEN  
        UPDATE SET tgt.addr = src.addr,tgt.CITY = src.CITY,tgt.ZIP = src.ZIP,tgt.PHONE = src.PHONE  
    WHEN NOT MATCHED THEN    
        INSERT (MID, MName, MDOB, Addr, City, ZIP, Phone)    
        VALUES (src.MID,src.MName, src.MDOB, src.Addr, src.City, src.ZIP, src.Phone);    
      
    select * from #FINALTable  
    

    Output:

    109183-output.png

    Best regards,
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

  2. Erland Sommarskog 100.7K Reputation points MVP
    2021-06-25T12:02:48.49+00:00

    1) if the Source column value is null then no need to update (only when matched)

    Here is a modification of Melissa's query which takes this in consideration.

    `
     MERGE #FINALTable AS tgt  
     USING #SourceTable AS src ON tgt.MID   = src.MID 
                              AND tgt.MName = src.MName 
                              AND tgt.MDOB  = src.MDOB  
     WHEN MATCHED AND NOT EXISTS (SELECT src.Addr, src.City, src.ZIP, src.Phone
                                  INTERSECT
                                  SELECT tgt.Addr, tgt.City, tgt.ZIP, tgt.Phone) THEN
         UPDATE
         SET    tgt.Addr  = isnull(src.Addr,  tgt.Addr),
                tgt.City  = isnull(src.City,  tgt.City),
                tgt.ZIP   = isnull(src.ZIP,   tgt.ZIP),
                tgt.Phone = isnull(src.Phone, tgt.Phone)
     WHEN NOT MATCHED THEN  
         INSERT (MID, MName, MDOB, Addr, City, ZIP, Phone)  
         VALUES (src.MID,src.MName, src.MDOB, src.Addr, src.City, src.ZIP, src.Phone);
    

    More precisely, I've added a condition to check whether there are any differences in matching rows. This comparison may seem alien to you. Rather than use a number of = conditions, I make use of the INTERSECT operator. This is because comparisons with NULL values will yield UNKNOWN when comparing with =. But INTERSECT handles NULL as any other value. My assumption is that when you have a NULL in the target column, and a non-NULL value in the Source column that you want replace the NULL with the source value.

    I have then added isnull to retain the value in the target column when the source table has NULL.

    how to check if there is any further validation required when inserting, for example only the first 5 characters if zip contains numeric values otherwise whole string etc..

    I*m afraid that I don't really understand what you have in mind here. Please bear in mind that we don't have any knowledge of your business rules.