May 26, 2021 at 5:02 am
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
May 26, 2021 at 6:04 am
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 );
May 26, 2021 at 6:29 am
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
May 26, 2021 at 6:32 am
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
May 26, 2021 at 6:36 am
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
May 26, 2021 at 9:11 am
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 26, 2021 at 3:16 pm
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
May 26, 2021 at 3:34 pm
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
May 26, 2021 at 7:18 pm
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 26, 2021 at 7:37 pm
Thank you Phil. Sure I will follow your guidelines and mindful next time when I Do
Thanks
Asiti
June 24, 2021 at 10:38 pm
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
June 24, 2021 at 10:49 pm
use a WHERE clause or a join in your UPDATE statement so the records you don't want updated don't get updated?
June 25, 2021 at 6:51 am
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
June 25, 2021 at 10:29 am
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
June 25, 2021 at 10:35 am
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
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply