Before I fall into the rabbit hole, I figured I’d ask if this is even possible. Every month I get a file that tells me which rows in Table A to delete and which rows from this file to append to Table A. Is Toad Automation Designer capable of automating this task? I know this is the bread and butter of SSIS but for the sake of argument, let’s say I don’t have SSIS privs.
Can you query on Table A?
If you can query on it, you can write code to delete and append,
Then automate your script
From: amlloyd [mailto:bounce-amlloyd@toadworld.com]
Sent: Wednesday, July 05, 2017 11:28 AM
To: toaddatapoint@toadworld.com
Subject: [Toad Data Point - Discussion Forum] Can Automation Designer Insert/Delete rows in a table based on a file?
Can Automation Designer Insert/Delete rows in a table based on a file?
Thread created by amlloyd
Before I fall into the rabbit hole, I figured I’d ask if this is even possible. Every month I get a file that tells me which rows in Table A to delete and which rows from this file to append to Table A. Is Toad Automation Designer capable of automating this task? I know this is the bread and butter of SSIS but for the sake of argument, let’s say I don’t have SSIS privs.
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.
Clarification – You would first have to tell it to import the file that needs to be linked to A to say which rows to delete and which rows to append.
It can all be automated.
From: amlloyd [mailto:bounce-amlloyd@toadworld.com]
Sent: Wednesday, July 05, 2017 11:28 AM
To: toaddatapoint@toadworld.com
Subject: [Toad Data Point - Discussion Forum] Can Automation Designer Insert/Delete rows in a table based on a file?
Can Automation Designer Insert/Delete rows in a table based on a file?
Thread created by amlloyd
Before I fall into the rabbit hole, I figured I’d ask if this is even possible. Every month I get a file that tells me which rows in Table A to delete and which rows from this file to append to Table A. Is Toad Automation Designer capable of automating this task? I know this is the bread and butter of SSIS but for the sake of argument, let’s say I don’t have SSIS privs.
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.
Well, I already have the script written that does the adds/deletes and I could just call that script in the autom,ation designer, but I was more wondering if this functionality is posible using the automation activities like the loops, if statements, etc. That way I can have better control over the flow, error reporting, etc.
Also on a side note, the file that needs to be loaded has a changing name. Is there a way to have automation designer prompt the user to select the file?
How does the name change? If there is a standard to the naming convention you can use masking to pull in any file like the mask. Or you can have a specific folder that only the file to be processed gets put into and have it process any file in that folder. I like to use one standard file name for processing and copy the current file changing the file name to the standard name so I only need one standard load file process that always points at the same name.
We have Data Compare tool. This lets you set up comparing rows from two disparate tables and compare the rows. The utility provides the generation and execution of update, insert, delete scripts. This can be used in automation. (Compare Data Activity).
If you are not comparing two existing tables you would need to build your own IN statement and use a delete with primary key. (Seems like a lot of work. Think I would try to use our data compare tool. Even if I had to build temporary table in Local Storage to compare with.
What type of compare tool are you using?
It is a module we wrote ourselves.
Ok. Thanks for the quick response. Have you tried using TOAD data compare automation?