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.
Need to share part of a process and an associated problem and see if some else has a perspective that will help me resolve the problem.
In a workflow I have a few steps that lead up to an Alteryx dataset. From it I 1) save the whole dataset as an excel file to indicate processing of records. 2) need to delete some rows from an inventory table based on a subset of the dataset. I believe the option given by Alteryx is to use the Output Data tool and put a query in the post create SQL statement .field such as 'delete from inventory where item in (select item from dataset)'.
I see two issues... the dataset is in Alteryx and I'm deleting from a table in sql server. I presume I can export from Alteryx to sql server as a temp table then use it to delete data from inventory table. Make sense? One more issue however is that using the Output Data tool wants to save data as part of the function of the tool but all I need the tool for is to execute a delete query. Is there a method to not have it save data but execute query with using post create sql statement or how do I fake the save data step?
Are there simpler ways to delete data in sql server from alteryx data?
If I move it from 'Post create SQL statement' to 'Pre create SQL statement' and try it, the error still comes back that it needs a table to append/update to. How do have it not insert/append but just do the delete?
I have a dataset that updates monthly and we need to delete records from our SQL DB, but we don't want to do a complete Truncate/Append as we need to keep the primary keys intact for the records already in there.
How I delete specific records in the SQL DB is to use a Formula tool to mark records for deletion (eg set SomeField = "DeleteMe"), write the updates back to the DB using the output tool, and then use the Post Create SQL Statement to delete them.
DELETE FROM TABLENAME WHERE TABLENAME.Comments = 'DeleteMe'
I think this is what another poster was getting at, but it wasn't quite as clear of an answer for me.