Delete From File Using Variable

Post any question you may have in regards to GoAnywhere Director and let our talented support staff and other users assist you.
4 posts Page 1 of 1

j_barnes

Posts: 2
Joined: Mon Jun 20, 2016 4:12 pm

Post by j_barnes » Mon Jun 20, 2016 4:19 pm
I see how to select records from table 1 into the "outputVariable" and then insert those records into table 2 using the "inputRowSetVariable". Is it also possible to delete the records from table 1 that were just inserted into table 2 using the same variable?

Support_Rick

Support Specialist
Posts: 592
Joined: Tue Jul 17, 2012 2:12 pm
Location: Phoenix, AZ

Post by Support_Rick » Tue Jun 21, 2016 8:21 am
Yes,

Just execute the SQL Task and execute a delete command utilizing the same where clause that you used to select the records for transfer. (No input/output variables are required for this)
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696

j_barnes

Posts: 2
Joined: Mon Jun 20, 2016 4:12 pm

Post by j_barnes » Tue Jun 21, 2016 8:46 am
Hi Rick,
I don't have a where clause so I'm worried that I'll delete new records that have been added. Let me explain in more detail because I may be using the wrong technique in GoAnywhere.

I'll be writing records to table 1 throughout the day. Periodically, likely every 30 seconds, we want to select all records from table 1 and insert them into table 2 and then delete the records that were moved from table 1. I need to make sure I'm not deleting any new records that were added to table 1 while the process is running.

Support_Rick

Support Specialist
Posts: 592
Joined: Tue Jul 17, 2012 2:12 pm
Location: Phoenix, AZ

Post by Support_Rick » Tue Jun 21, 2016 9:39 am
j_barnes,

That's a good reason to be worried about what you're doing. You will have this issue whether you're using GoAnywhere or any other product though.

Your key factor here is identifying which records were pulled (transferred to the other database). You'll need to figure out some sort of functionality to identify these records for the Delete statement. This could be making a temp table of the ones you're transferring, then stating a "Delete from table where record exists in temp.table", then when all records have been transferred, then deleted .. remove the Temp.Table.

If they are date/time stamped, delete where records are older than the last time of transfer. Delete by record number. Or, transfer them record by record deleting after transfer.

The key here is knowing how to identify the record(s) that have been transferred so that you can delete them specifically, not a "Delete * from <table>".

Hope this makes sense.
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
4 posts Page 1 of 1