Hi, I'm searching for a way to simply execute generic SQL commands against a server. Specifically, I'd like to execute some stored procedures on the server after loading data into staging tables using alteryx.
I know you can use custom SQL as part of data input connectors, but I'm just looking for a way to run other SQL statements against a server such as exec and delete statements, etc.
Solved! Go to Solution.
I have this same question.
Yep.. same question... Anyone???
The typical way you pass parameters to a Stored Proc using Alteryx is through the Dynamic input tool.
You would set up the Source Template to point to the SP and then Add a "SQL:Update Stored Procedure" that would feed parameters from a precedent tool (e.g., Text Input).
Greetings !
Another way that has worked for me was updating the Post/Pre-SQL Statement with batch macros control parameters. Under the Interface tool, the Control parameter tool would be connected to the Input tool and after selecting the Store Procedure command, leave only the string to replace in the Replace Specific String. An article on batch macros can be found here: http://community.alteryx.com/t5/Alteryx-Knowledge-Base/Batch-Macros-An-Example/ta-p/1137
So let me get this straight. There is actaully no generic "Run SQL command" tool???
That is one hell of an oversight folks! someone should fix that ASAP I think!
Here is my situation:
I am bulk loading to a database via batch macro, running it about 100 times (one each for rows in another table) and using this to create 100 tables to apend to a database
What I needed was a way to first truncate the table prior to loading my batches
I ended up creating a "Block until Done" with a dummy table that unnecessarily copied the batches just so i could use the "Pre Create SQL Statement"
Ok I am a newby , please tell me there is a better way??
I'm not totally clear on what your process is doing from your description, but it sounds like this...
If that is correct, then can't you just put the following instructions in the Pre Create SQL Statement in the Input tool (which I assume is pointing to the BatchRuns table) that feeds into the batch macro?
Drop Table BatchRuns
Select * into BatchRuns from "wherever you new batch list is"
Apologies as well, my original post had a screenshot that I couldnt get to upload here, and I realise there was things in that shot that would have helped explain this problem a little better
I am first (through the "Block Until Done") taking the source, table information, then COPYING this BATCHRUNS table to a second dummy table (which is exactly the same as the source table) with an "Output Data" tool, all JUST so that I can use the "pre create SQL statement" to use a command to delete the "BATCH TARGET" table.
but I have created an unnecessary dummy table to achieve something that ought to be simple. SSIS has a task to run SQL statements, there should be one in Alteryx,
will TRY again to send a screenshot of what I did