Delete From File Using Variable

[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]

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)

[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]

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.