Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Can you execute generic SQL commands?

dustin
5 - Atom

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.

26 REPLIES 26
MargaritaW
Alteryx Alumni (Retired)
Hello !

Yes, you can execute stored procedures with Alteryx after outputting your data with the Output Data tool in Alteryx, there is an option in the Output tool for Post-SQL statement, you could write the EXEC stored_procedure_name there or you could execute your DELETE statement. The Post-SQL statement runs after the output, there is also a Pre-SQL option that would execute before the output is written.
Margarita Wilshire
Sr. Manager, Customer Support Engineering
Women & Allies Committee


Alteryx, Inc.
sandeep_chayanam
8 - Asteroid
Hi gents,
Is there a way to pass in values to parameters of a stored proc in Pre/Post SQL statements in Input/Output tools?


Thanks,
Sandeep.
mkawayoshi
5 - Atom

I have this same question.

umermariner
5 - Atom

Yep.. same question... Anyone???

RodL
Alteryx Alumni (Retired)

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).

MargaritaW
Alteryx Alumni (Retired)

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

Margarita Wilshire
Sr. Manager, Customer Support Engineering
Women & Allies Committee


Alteryx, Inc.
JohnY
7 - Meteor

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??

 

RodL
Alteryx Alumni (Retired)

@JohnY,

I'm not totally clear on what your process is doing from your description, but it sounds like this...

 

  • You have a table (call it BatchRuns) that contains a set of records that has a unique table name and source for data in each record.
  • You use that table to run a batch macro (in Alteryx?) to load the data identified in each source into the corresponding unique table name into a database.
  • You need to eliminate all of the records in the BatchRuns table first and then reload it with new data each time prior to running the batch process.

 

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"

 

JohnY
7 - Meteor
  • You have a table (call it BatchRuns) that contains a set of records that has a unique table name and source for data in each record. – CLOSE ENOUGH, BUT LETS SAY IT HAS TABLE NAMES AS A SOURCE, AND THERE ARE 100 TABLES THAT ARE SOURCE (ACTUALLY THEY ARE STRING INFORMATION TO CREATE 100 URLS FOR API WEB CALLS, BUT THEY CREATE A SET OF DATA FOR EACH CALL, ALL OF IDENTICAL STRUCTURE, SO THINK OF THEM AS TABLES)
  • You use that table to run a batch macro (in Alteryx?) to load the data identified in each source into the corresponding unique table name into a database. NOPE, AND THIS IS THE ISSUE. ALL 100 SOURCE TABLES GET APPENDED TO A SINGLE TARGET TABLE. LETS CALL IT “BATCH TARGET”
  • You need to eliminate all of the records in the BatchRuns table first and then reload it with new data each time prior to running the batch process. NO. PRIOR TO LOADING A BATCH OF 100 TABLES TO ONE TARGET TABLE, I NEED TO CLEAR THE ONE BATCH TARGET TABLE PRIOR TO EACH LOAD

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

Labels