I want to upload data to a current table by merging it and not simply adding the data. I am in Toad Data Point 3.6. Is there a way to do this successfully? (If you tell me to upgrade to the current version, that gives me other issues including absolutely no automation functionality. And yes, I did ask support and didn’t get it resolved so I stayed with 3.6 since automation does function for me there.)
Thanks for any help you can offer.
Susie
Hello,
I’m not sure what you exactly mean by merging the data (do you want to just insert the missing rows only, or do you need to be able to solve conflicts).
But have you tried to use the Compare Data activity? You can define there source and target schema and after that you can select the table(s) you want to compare. This will generate Data Compare synchronization script. You can check the script and / or execute it immediately.
Libor
I want to upload the data and update it if it already exists in the table. If not, add. I want to do this via automation so I don’t have to touch it and it can do it on off peak hours for the database. I am not comparing any tables.
From: Libor.Witasek [mailto:bounce-LiborWitasek@toadworld.com]
Sent: Tuesday, October 31, 2017 7:12 AM
To: toaddatapoint@toadworld.com
Subject: RE: [Toad Data Point - Discussion Forum] Merging Data in Oracle Table during Automation
RE: Merging Data in Oracle Table during Automation
Reply by Libor.Witasek
Hello,
I’m not sure what you exactly mean by merging the data (do you want to just insert the missing rows only, or do you need to be able to solve conflicts).
But have you tried to use the Compare Data activity? You can define there source and target schema and after that you can select the table(s) you want to compare. This will generate Data Compare synchronization script. You can check the script and / or execute it immediately.
Libor
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or
Unsubscribe from Toad Data Point Forum notifications altogether.
Toad Data Point - Discussion Forum
Flag
this post as spam/abuse.
The information contained in this message is privileged and intended only for the recipients named. If the reader is not a representative of the intended recipient, any review, dissemination or copying of this message or the information it contains is prohibited. If you have received this message in error, please immediately notify the sender, and delete the original message and attachments.
Hello,
I meant that you can consider the input data as a source and target table as a target in the data compare. Then it should generate the script, which inserts or updates data from source to target.
The only thing you need to do is to ensure, that the data compare won’t delete rows from target, which aren’t in source. I think this can be defined in the Select Comparison Options (Record Types to Include in Comparison).
Libor
Oh, okay. Thanks I will try it when I have time.
From: Libor.Witasek [mailto:bounce-LiborWitasek@toadworld.com]
Sent: Tuesday, October 31, 2017 9:22 AM
To: toaddatapoint@toadworld.com
Subject: RE: [Toad Data Point - Discussion Forum] Merging Data in Oracle Table during Automation
RE: Merging Data in Oracle Table during Automation
Reply by Libor.Witasek
Hello,
I meant that you can consider the input data as a source and target table as a target in the data compare. Then it should generate the script, which inserts or updates data from source to target.
The only thing you need to do is to ensure, that the data compare won’t delete rows from target, which aren’t in source. I think this can be defined in the Select Comparison Options (Record Types to Include in Comparison).
Libor
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or
Unsubscribe from Toad Data Point Forum notifications altogether.
Toad Data Point - Discussion Forum
Flag
this post as spam/abuse.
The information contained in this message is privileged and intended only for the recipients named. If the reader is not a representative of the intended recipient, any review, dissemination or copying of this message or the information it contains is prohibited. If you have received this message in error, please immediately notify the sender, and delete the original message and attachments.
MIght it work to use Oracle’s MERGE statement. I’ve found it very useful.
Do you have some examples on how this will work? I am using Toad Data Point 3.6 so I need examples from that version. I can’t get newer versions to automate at all. I have worked with both the Toad Data Point help and my company’s help desks and no one can seem to give me any answers on why automation won’t work at all in versions higher than 3.6. I didn’t think version 3.6 had a merge feature but that only newer versions have it. I have asked about a merge feature from Toad Data Point help.
From: ken.gondor [mailto:bounce-kengondor@toadworld.com]
Sent: Thursday, January 04, 2018 9:04 AM
To: toaddatapoint@toadworld.com
Subject: RE: [Toad Data Point - Discussion Forum] Merging Data in Oracle Table during Automation
RE: Merging Data in Oracle Table during Automation
Reply by ken.gondor
MIght it work to use Oracle’s MERGE statement. I’ve found it very useful.
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or
Unsubscribe from Toad Data Point Forum notifications altogether.
Toad Data Point - Discussion Forum
Flag
this post as spam/abuse.
The information contained in this message is privileged and intended only for the recipients named. If the reader is not a representative of the intended recipient, any review, dissemination or copying of this message or the information it contains is prohibited. If you have received this message in error, please immediately notify the sender, and delete the original message and attachments.
MERGE is actually an Oracle function and has nothing to do with TOAD. You can find the documentation and an example of a MERGE statement HERE in the documentation.
Thanks! Maybe I can get this to work for me. I appreciate your time and effort.
From: john.mcgraw [mailto:bounce-johnmcgraw@toadworld.com]
Sent: Thursday, January 04, 2018 11:14 AM
To: toaddatapoint@toadworld.com
Subject: RE: [Toad Data Point - Discussion Forum] Merging Data in Oracle Table during Automation
RE: Merging Data in Oracle Table during Automation
Reply by john.mcgraw
MERGE is actually an Oracle function and has nothing to do with TOAD. You can find the documentation and an example of a MERGE statement
HERE in the documentation.
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or
Unsubscribe from Toad Data Point Forum notifications altogether.
Toad Data Point - Discussion Forum
Flag
this post as spam/abuse.
The information contained in this message is privileged and intended only for the recipients named. If the reader is not a representative of the intended recipient, any review, dissemination or copying of this message or the information it contains is prohibited. If you have received this message in error, please immediately notify the sender, and delete the original message and attachments.
I requested a simple add/update function and compare was suggested. I looked into it and it really isn’t a good way to mimic the request.
I also mentioned that this simple function would really put TDP on the map. I really hope that the company looks into this as it’s own function - would make a HUGE difference.
hi all,
below, an example of MERGE statement, I hope this help…
MERGE INTO
IMMOVAL_MAPP_LOCBAIL IML1 – TARGET TABLE
USING
(SELECT
IMLN.BAIL_DIRECT,
IMLN.BAIL_GEST_NO,
IMLN.BAIL_IMME_NO,
IMLN.BAIL_NO,
IMLN.BAIL_LOCA_NOM,
IMLN.BAIL_LOTP,
IMLN.GLOL_LOTS_NO,
IMLN.LOTS_DESIGNATION,
IMLN.LOTS_SURFACE,
IMLN.GEST_EXT,
IMLN.IMME_NO_EXT,
IMLN.LOCAT_EXT,
IMLN.BAIL_EXT,
IMLN.“LOCA NOM EXT”,
IMLN.BAIL_GLOG_DIRECT,
IMLN.BAIL_ENTREE_DATE,
IMLN.BAIL_SORTIE_V_DATE,
IMLN.“TYPE LOT”
FROM
IMMOVAL_MAP_LOCBAIL_NEW IMLN --SOURCE TABLE
WHERE
IMLN.LOCAT_EXT <> ‘DESYNC’ and IMLN.BAIL_EXT <> ‘DESYNC’) NOUVEAU
ON
(IML1.BAIL_DIRECT||’’||IML1.GLOL_LOTS_NO = NOUVEAU.BAIL_DIRECT||’’||NOUVEAU.GLOL_LOTS_NO)
WHEN MATCHED
THEN
UPDATE SET IML1.LOCAT_EXT = NOUVEAU.LOCAT_EXT,
IML1.BAIL_EXT = NOUVEAU.BAIL_EXT
WHEN NOT MATCHED
THEN
INSERT(
IML1.BAIL_DIRECT,
IML1.BAIL_GEST_NO,
IML1.BAIL_IMME_NO,
IML1.BAIL_NO,
IML1.BAIL_LOCA_NOM,
IML1.BAIL_LOTP,
IML1.GLOL_LOTS_NO,
IML1.LOTS_DESIGNATION,
IML1.LOTS_SURFACE,
IML1.GEST_EXT,
IML1.IMME_NO_EXT,
IML1.LOCAT_EXT,
IML1.BAIL_EXT,
IML1.“LOCA NOM EXT”,
IML1.BAIL_GLOG_DIRECT,
IML1.BAIL_ENTREE_DATE,
IML1.BAIL_SORTIE_V_DATE)
VALUES
(
NOUVEAU.BAIL_DIRECT,
NOUVEAU.BAIL_GEST_NO,
NOUVEAU.BAIL_IMME_NO,
NOUVEAU.BAIL_NO,
NOUVEAU.BAIL_LOCA_NOM,
NOUVEAU.BAIL_LOTP,
NOUVEAU.GLOL_LOTS_NO,
NOUVEAU.LOTS_DESIGNATION,
NOUVEAU.LOTS_SURFACE,
NOUVEAU.GEST_EXT,
NOUVEAU.IMME_NO_EXT,
NOUVEAU.LOCAT_EXT,
NOUVEAU.BAIL_EXT,
NOUVEAU.“LOCA NOM EXT”,
NOUVEAU.BAIL_GLOG_DIRECT,
NOUVEAU.BAIL_ENTREE_DATE,
NOUVEAU.BAIL_SORTIE_V_DATE)
– WHERE
– (field = condition);
Regards.
Franck
Thank you.
From: SQL_Franck [mailto:bounce-mercier104_000@toadworld.com]
Sent: Tuesday, January 16, 2018 5:40 AM
To: toaddatapoint@toadworld.com
Subject: RE: [Toad Data Point - Discussion Forum] Merging Data in Oracle Table during Automation
RE: Merging Data in Oracle Table during Automation
Reply by SQL_Franck
hi all,
below, an example of MERGE statement, I hope this help…
MERGE INTO
IMMOVAL_MAPP_LOCBAIL IML1 – TARGET TABLE
USING
(SELECT
IMLN.BAIL_DIRECT,
IMLN.BAIL_GEST_NO,
IMLN.BAIL_IMME_NO,
IMLN.BAIL_NO,
IMLN.BAIL_LOCA_NOM,
IMLN.BAIL_LOTP,
IMLN.GLOL_LOTS_NO,
IMLN.LOTS_DESIGNATION,
IMLN.LOTS_SURFACE,
IMLN.GEST_EXT,
IMLN.IMME_NO_EXT,
IMLN.LOCAT_EXT,
IMLN.BAIL_EXT,
IMLN.“LOCA NOM EXT”,
IMLN.BAIL_GLOG_DIRECT,
IMLN.BAIL_ENTREE_DATE,
IMLN.BAIL_SORTIE_V_DATE,
IMLN.“TYPE LOT”
FROM
IMMOVAL_MAP_LOCBAIL_NEW IMLN --SOURCE TABLE
WHERE
IMLN.LOCAT_EXT <> ‘DESYNC’ and IMLN.BAIL_EXT <> ‘DESYNC’) NOUVEAU
ON
(IML1.BAIL_DIRECT||’’||IML1.GLOL_LOTS_NO = NOUVEAU.BAIL_DIRECT||’’||NOUVEAU.GLOL_LOTS_NO)
WHEN MATCHED
THEN
UPDATE SET IML1.LOCAT_EXT = NOUVEAU.LOCAT_EXT,
IML1.BAIL_EXT = NOUVEAU.BAIL_EXT
WHEN NOT MATCHED
THEN
INSERT(
IML1.BAIL_DIRECT,
IML1.BAIL_GEST_NO,
IML1.BAIL_IMME_NO,
IML1.BAIL_NO,
IML1.BAIL_LOCA_NOM,
IML1.BAIL_LOTP,
IML1.GLOL_LOTS_NO,
IML1.LOTS_DESIGNATION,
IML1.LOTS_SURFACE,
IML1.GEST_EXT,
IML1.IMME_NO_EXT,
IML1.LOCAT_EXT,
IML1.BAIL_EXT,
IML1.“LOCA NOM EXT”,
IML1.BAIL_GLOG_DIRECT,
IML1.BAIL_ENTREE_DATE,
IML1.BAIL_SORTIE_V_DATE)
VALUES
(
NOUVEAU.BAIL_DIRECT,
NOUVEAU.BAIL_GEST_NO,
NOUVEAU.BAIL_IMME_NO,
NOUVEAU.BAIL_NO,
NOUVEAU.BAIL_LOCA_NOM,
NOUVEAU.BAIL_LOTP,
NOUVEAU.GLOL_LOTS_NO,
NOUVEAU.LOTS_DESIGNATION,
NOUVEAU.LOTS_SURFACE,
NOUVEAU.GEST_EXT,
NOUVEAU.IMME_NO_EXT,
NOUVEAU.LOCAT_EXT,
NOUVEAU.BAIL_EXT,
NOUVEAU.“LOCA NOM EXT”,
NOUVEAU.BAIL_GLOG_DIRECT,
NOUVEAU.BAIL_ENTREE_DATE,
NOUVEAU.BAIL_SORTIE_V_DATE)
– WHERE
– (field = condition);
Regards.
Franck
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or
Unsubscribe from Toad Data Point Forum notifications altogether.
Toad Data Point - Discussion Forum
Flag
this post as spam/abuse.
The information contained in this message is privileged and intended only for the recipients named. If the reader is not a representative of the intended recipient, any review, dissemination or copying of this message or the information it contains is prohibited. If you have received this message in error, please immediately notify the sender, and delete the original message and attachments.