January 29, 2019 at 7:19 am
I have a stage where data ported from one source and it needs to be loaded in detail table based on condition.
1) If stage Load and Detail load date match then don't load existing ID in detail
2) If stage load and detail load dates are mismatch then load ID 2nd time
Any help much appreciated
Thanks,
Koti Raavi.
January 29, 2019 at 8:16 am
Are you unsure of how to do this?
insert target
select columns from source s
inner join target t
on s.date <> t.date
January 29, 2019 at 9:22 am
Depending on what you are loading you should look at a LEFT OUTER JOIN or an EXCEPT.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 29, 2019 at 9:34 am
drew.allen - Tuesday, January 29, 2019 9:22 AMDepending on what you are loading you should look at a LEFT OUTER JOIN or an EXCEPT.Drew
Yes,how we can handle date as well. For ID below query will work, We should check ID and date both, if ID exist today then no need of load and tomorrow we can still load one time as it is fresh load..hope it is clear
Select * FROM Stage S
LEFt Join Detail H
On H.ID =S.ID
Where H.ID is null
January 29, 2019 at 10:01 am
koti.raavi - Tuesday, January 29, 2019 9:34 AMdrew.allen - Tuesday, January 29, 2019 9:22 AMDepending on what you are loading you should look at a LEFT OUTER JOIN or an EXCEPT.Drew
Yes,how we can handle date as well. For ID below query will work, We should check ID and date both, if ID exist today then no need of load and tomorrow we can still load one time as it is fresh load..hope it is clear
Select * FROM Stage S
LEFt Join Detail H
On H.ID =S.ID
Where H.ID is null
You said it yourself. "We should check ID and date both." You are only checking the ID.
SELECT *
FROM Stage S
LEFT OUTER JOIN Detail D -- Don't know why you used H here
ON D.ID = S.ID
AND D.YourDateField = S.YourDateField
WHERE D.ID IS NULL
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply