How to insert data to target table when there is no matching and update if has

Question

Post reply

How to insert data to target table when there is no matching and update if has

asita

SSCarpal Tunnel

Points: 4144
More actions
May 26, 2021 at 5:02 am

#3888027

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 exist in Target then if there is any values between source and target then need to update Target with source values
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

Viewing 15 posts - 1 through 15 (of 15 total)

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

DesNorton SSC-Insane Points: 24399 More actions · Answer 1

Please read this article on upserts and concurrency http://source.entelect.co.za/why-is-this-upsert-code-broken

From the above, you should end up with code that looks like this

UPDATE      dst
SET         dst.Addr  = src.Addr
          , dst.City  = src.City
          , dst.ZIP   = src.ZIP
          , dst.Phone = src.Phone
FROM        #SourceTable AS src
INNER JOIN  #FINALTable  AS dst
        ON src.MID   = dst.MID
       AND src.MName = dst.MName
       AND src.MDOB  = dst.MDOB;

INSERT INTO #FINALTable ( MID, MName, MDOB, Addr, City, ZIP, Phone )
SELECT  src.MID
      , src.MName
      , src.MDOB
      , src.Addr
      , src.City
      , src.ZIP
      , src.Phone
FROM    #SourceTable AS src
WHERE   NOT EXISTS ( SELECT 1 FROM #FINALTable AS dst WITH (XLOCK, HOLDLOCK)
                     WHERE  src.MID   = dst.MID
                       AND  src.MName = dst.MName
                       AND  src.MDOB  = dst.MDOB );

How to post data/code on a forum to get the best help.

asita SSCarpal Tunnel Points: 4144 More actions · Answer 2

Thank you Desnorton, for the Query.

Quick Check. in source table every column is vacrhar(500) but in the Target table it is DOB is date , Phone Number fields

In this case can I use before update/Insert the data is convertible without error then only insert/update into target. how can i handle that in a efficient manner? please (such as IsDate, Is Numeric etc)

Much appreciate your help in this

Thanks

Asiti

asita SSCarpal Tunnel Points: 4144 More actions · Answer 3

This may give you clarification

assume the table structure as below, when there is invalid date value in #SourceTable such as 21/21/2001 then we simply discard the data for being updating / inserting looking for the script should not fail/break with invalid values. it just eliminate invalid values for being storing.

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

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

Thank you

Asiti

DesNorton SSC-Insane Points: 24399 More actions · Answer 4

I would change the data types in the #SourceTable. Or at least create another intermediate process that cleans/types the data. Otherwise, you are asking for a world of hurt

How to post data/code on a forum to get the best help.

Phil Parkin SSC Guru Points: 246985 More actions · Answer 5

To the OP: why did you cross-post? You have enough points to know better. Do you realise that this annoys those people who take the time to answer in one thread, only to find that they've wasted their time answering the other thread?

The original question is here, along with various other answers, which you appear to have ignored.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

asita SSCarpal Tunnel Points: 4144 More actions · Answer 6

Apologize Phil.

I posted in the other post first but realized it is not the correct place for it as that is discussion not relate to Coding perspective. so I then realized and created new post (this one).

I cleaned the other post entry.

let me know if you wish me to do any further. Apologize Again All

Thank you

Asiti

asita SSCarpal Tunnel Points: 4144 More actions · Answer 7

Thank you DesNorton.

Can you give some guide lines... how to validate (better ones may be try_convert or ISDate or some other efficient way before loading to intermediately table) and load to intermediate table vs doing in the same script?

Thanks

Asiti

Phil Parkin SSC Guru Points: 246985 More actions · Answer 8

asita wrote:

Apologize Phil.
I posted in the other post first but realized it is not the correct place for it as that is discussion not relate to Coding perspective. so I then realized and created new post (this one).
I cleaned the other post entry.
let me know if you wish me to do any further. Apologize Again All
Thank you
Asiti

Thanks for taking the time to write this out. I understand why you thought that making another post was a reasonable idea and it would have been 100% OK if you had mentioned the fact in the first thread ... something like

"I have created a new thread here (link) relating to this exact requirement"

Now the people who answered thread #1 know that things are continuing in thread #2 and all is well. Maybe bear this in mind in future. Good luck solving your problem.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

asita SSCarpal Tunnel Points: 4144 More actions · Answer 9

Thank you Phil. Sure I will follow your guidelines and mindful next time when I Do

Thanks

Asiti

asita SSCarpal Tunnel Points: 4144 More actions · Answer 10

Good Evening,

Here in this process, how do we check to make sure we are not updating target with the source null values (when target has a value already) also only do update if there is a change.

for example. here in this example we dont want to update target.ZIP value (which is having some value already)

with source zip has null value, in this case we dont want to update.

also for some date column, we want to make sure the src date column value is proper one such as data value not > todays date etc..

so in this case can i write in one statement or in multiple statements any advise or guidance ? please

Thank you

Asita

pietlinden SSC Guru Points: 63506 More actions · Answer 11

use a WHERE clause or a join in your UPDATE statement so the records you don't want updated don't get updated?

DesNorton SSC-Insane Points: 24399 More actions · Answer 12

You could use ISNULL for your update, so that it does not replace existing values with NULL.

Something like this ....

UPDATE dest
SET dest.Value = ISNULL(src.Value, Dest.Value)
FROM SourceTable AS src
JOIN DestinationTable AS dest ON src.Key = dest.key

How to post data/code on a forum to get the best help.

asita SSCarpal Tunnel Points: 4144 More actions · Answer 13

Thank you DesNorton,

I got that, my question was how can I incorporate that in this single statement, that null values update always need to check for all columns, if source columns value is null or not, if so then no update on that column.

the reason is I will be having a column recordmodifieddate that need to be capture when there is any one column values is updated (in whole record) from src to target with some value change..

can you please advise

Thankyou in advance

asiti

asita SSCarpal Tunnel Points: 4144 More actions · Answer 14

Thanks PietLinden.

I can do that Individual updates for each column as Des mentioned. how to capture the LastRecordModifiedDate column values (sorry not part of original Question).

so this LastRecordModifiedDate need to fill in when there is actual column values is updated (it has different values in both src and target and get updates then only will capture it).

any advise on below part.. in terms of optimization etc,,

UPDATE TGT

SET TGT.COLUMN1 = SRC.Column1

from Table1 TGT JOIN TableSRC src on TGT.Key1 = SRC.Key1

and NVL(TGT.Column1,'') <> NVL(SRC.Column1,'')

Thanks

ASiti