mergining between source and target tables

Question

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

Answer 1

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.

Answer 2

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:

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.

Answer 3

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.