Skip to content

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.
  • j_barnes Offline
  • Posts: 2
  • Joined: Mon Jun 20, 2016 4:12 pm

Delete From File Using Variable

Post by j_barnes »

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 Offline
  • Support Specialist
  • Posts: 590
  • Joined: Tue Jul 17, 2012 2:12 pm
  • Location: Phoenix, AZ
  • Contact:

Re: Delete From File Using Variable

Post by Support_Rick »

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 Offline
  • Posts: 2
  • Joined: Mon Jun 20, 2016 4:12 pm

Re: Delete From File Using Variable

Post by j_barnes »

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 Offline
  • Support Specialist
  • Posts: 590
  • Joined: Tue Jul 17, 2012 2:12 pm
  • Location: Phoenix, AZ
  • Contact:

Re: Delete From File Using Variable

Post by Support_Rick »

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
Post Reply