This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.