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!
The Product Idea boards have gotten an update to better integrate them within our Product team's idea cycle! However this update does have a few unique behaviors, if you have any questions about them check out our FAQ.

Alteryx Designer Desktop Ideas

Share your Designer Desktop product ideas - we're listening!
Submitting an Idea?

Be sure to review our Idea Submission Guidelines for more information!

Submission Guidelines

Have an SQL Tool

I constantly find my using pre and post SQL Commands in the Output tool to run SQL when I don't actually have any data to output.

 

One example is when I load data into S3 and want to load it into Redshift. I have SQL code to run but no data to Output - I end up running a dummy row into a temp table.

 

So can we have an SQL tool that simply acts the same as a Pre-SQL command without the associated data output. Once the command is run we should be able to continue the workflow, so the tool should have an option input and output, like the Run Command tool.

 

 

32 Comments
ShaneGraham
7 - Meteor

I agree 100 percent with the original poster and @r4upadhye in that a SQL tool would be invaluable.  It would be great to be able to execute insert/update/delete/DDL or PL/SQL SQL statements in the middle of a workflow, and have the SQL be update-able based on data in the stream, like the Dynamic Input tool.

 

I hope Alteryx will re-consider the need for this tool.

Kieran
7 - Meteor

Really disappointing that this feature won't be implemented. There are many cases were a generic piece of SQL needs to be run, rather than splitting the entire script out into possible (likely) many many tools in Alteryx.

For instance I just had a scenario where I need to update one field in a table with 16 million rows based on a join with a different table. This would be a couple of lines of code in SQL but in Alteryx, I'd have to either use the POST/PRE Create SQL statements, or drop and recreate the entire table.

 

Hardly an intuitive solution, more of a work around due to Alteryx limitations.

ARich
Alteryx Alumni (Retired)
Status changed to: Comments Requested

Hi All,

 

We're re-evaluating this, but trying to better understand what the underlying problem and use case is that that majority of users are experiencing. We have an initial implementation in mind, but would love to hear direct feedback on it. Please DM me if you're willing to talk through your use case and give feedback on a proposed solution.

 

Best,

Alex

r4upadhye
11 - Bolide

I will briefly explain my requirements here.

 

I have to manage several ad-hoc queries to seamlessly deliver our daily batch reports.

I often use Alteryx flow as a gold standard to validate my sql query's output.

 

However, only if Alteryx showed me its underlying sql query for a given workflow, I would save a lot of time by skipping my manual query...

 

jroot
8 - Asteroid

For interacting with a variety of SQL based databases like Snowflake, Postgres, MySQL it'd be great to be able to interact with those systems without the requirement of having to do a data output. As the original poster said it comes up fairly often that you may desire to push a command to a DB, but don't necessarily have data to export to that system.

SeanAdams
17 - Castor
17 - Castor

Another example is when you are doing a basic dimension load.   One of the first things you need to do is to check that the table exists, and if not then create it.

Another is when you want to mark the latest version of a fact table

Another is when you need to create an aggregate entry in a fast aggregate table, after loading a detailed fact table

Another is when you need to create a new entry in the "Data loader" table; get this latest data load ID; and then use this in all your downstream output tools.

 

All of these are common things that we do every day, and right now they are embedded deep inside pre or post SQL commands which makes them inherently difficult to maintain, hides the functionality - and makes this needlessly more complex.

 

What would be good is a SQLCommand tool that allows you to execute ANY SQL commands you want to, and then choose whether you want to pass through all the fields that were given as input (this is important if you're doing a table create or a table truncate - the response from this in SQL is just "query completed"), or if you want to include results that come back from the SQL command or a combination.

 

It would also be VERY useful if this SQL command tool allowed dynamic actions like the Dynamic input tool.

 

Very keen on this - and would be happy to bring together a set of users from our company on a live call to work this through with the Alteryx team.

 

ARich
Alteryx Alumni (Retired)
Status changed to: Under Review

Hi All,

 

We're currently evaluating this and other alternatives to achieve the majority of the use cases.

 

Best,

Alex

mcarrico
9 - Comet

Our company ended up building out this functionality using macros wrapped around a Run Command tool calling a parameterized batch script.  This macro gets used extensively, but I would love to see an officially supported tool that didn't require internal maintenance.  I would be happy to walk through our use cases and implementation on a call with Alteryx if you are still looking for input on this.

jcardoso
8 - Asteroid

Hi,

if the issue is to run queries which have to be dynamically built during some process,  a very simple batch macro over an Input Tool with an update value Action, like odbc: DSN=<mydatabase>|||<myquery> will sort it.

Regards.

behring
5 - Atom

Hello everyone,

 

For those users who have solved this via a Macro, will it be possible to share it with the community to see your implementation?

 

In my case, I created an R script to Call Oracle Stored procedures and Dynamic SQL statements using parameters/input from other tools but is not neat nor easy to follow. 

 

What is surprising is that after 2 1/2 years since the original idea, this function/option is still under review. 

Maybe there are not many users using Alteryx as an ETL tool.

 

Scenarios:

1)

* I need to get a value from a Table (MySQL)

* This value will be used within an Input Data, which connects to another source (SQL SERVER)

2) 

* after transforming my input data and loading it into a Staging table in our database,

I need to run a particular SQL script  (i.e. MERGE statement) that uses this new load data

 

 

Thank you all