Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Deletion of records from a table

jeffv
8 - Asteroid

All,

 

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?

 

Thanks,

Jeff

 

 

4 REPLIES 4
cmcclellan
13 - Pulsar

I've used the "export from Alteryx to sql server as a temp table then use it to delete data from inventory table" a lot.  In this way:

 

- add a "block until done" tool and then write records to the temp table

- in your final output as "pre-commit SQL" (might not be that, but I can't confirm now) and include the "delete from ....." and reference the keys in your temp table from previously.

 

It works fine 🙂 

jeffv
8 - Asteroid

Thanks for response!

 

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?

 

Thanks,

Jeff

cmcclellan
13 - Pulsar

Ah, right, only deleting records.

 

That is a bit different.  Maybe use Dynamic Input like this : https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Delete-database-records/td-p/39929

michael_team
7 - Meteor

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.

 

Hope this helps someone 🙂

 

Labels