community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Designer Ideas

Share your Designer product ideas - we're listening!
Community v19.6

Looks aren't everything... But the latest Community refresh looks darn good!

Learn More

Enclose Database delete data and append in a transaction

When using the SQL output "delete and append" option, I noticed that this is not transactional. I.e. it deletes the data first, then inserts the new data. If an issue happens which prevents the new data insert from happening, then you have lost all the data in the table but it hasn't been replaced by anything new.

 

I tested this by revoking insert permissions from the login I was using - the insert failed, but the delete had already occurred. This could also occur if there was a network error or connection drop in the middle of the execution.

 

I use delete and append because the replace if new option is unutterably slow (takes about 5 minutes to complete on ~3000 rows, instead of 0.5 seconds)

 

I think the delete and append  option should either be enclosed in an explicit transaction, or a combination of temp tables and copying  should be employed. This behaviour could maybe be offered as an option in case of extremely large datasets.