Can you execute generic SQL commands?

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.

Input

Output

Database Connection

Accepted answers

MargaritaW

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.

All comments

MargaritaW

sandeep_chayanam

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

I have this same question.

MargaritaW

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

JohnY

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

@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"

JohnJPS

Just an aside: you could use sqldf (or maybe RODBC) in the R Command tool if you wanted to simply execute some SQL.

You could use that (I believe) if you wanted to do something like drop a table.

travist6983

Hello MargaritaW - i know this is an old ticket but i am trying to do what you suggested and maybe my version of Alteryx is different than when this was written but I dont see Post-SQL Statment. I see Post Create SQL Statement which i am assuming only works when you are creating a table which i am using append to existing.

What would be the best way to execute this SQL block

DECLARE
n_SL_ID number;
BEGIN
select max(sl_id) into n_SL_ID from source_log where sj_id = (select sj_id from source_job sj where sj.sj_code = 'GOOGLEANALYTICS');
wa_di_pkg.ga_post_process(n_SL_ID);
END;

Or how could I set a variable to be passed into my procedure and make the block simplier so it would be something like this...

begin
wa_di_pkg.ga_post_process(113)
end;

Where 113 would be a variable.

UpendraPandey

Hello @MargaritaW.

I disagree with you statement that "The Post-SQL statement runs after the output".

When i am running my Stored Procedure in Post SQL. My Stored procedure is getting kicked off before my Output load is done.

Here is the Screen shot for the same. Do you have any idea how i can create dependencies to execute stored proc once my Output load is done.

byarsmc79

I have been using both the pre and post SQL command options, but I noticed that now they do not always execute in order anymore. i.e. the post sql statements are ran prior to the writing the output. This causes quite an issue with my data. Is this a known issue already? Also, if you notice in the below example, the pre-sql is being ran even before the input data is being read. This can cause an issue if the process is canceled.screen shot showing the output going out of order

ak2018

have you tried using Dynamic input tool instead? You can replace certain strings with the arguments you want to pass. I do that for dynamic sql, but for stored procedures it can be done in similar fashion.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703