Have an SQL Tool

[chris_love]

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.

[Atabarezz]

I'm sure you are aware there happens to be new tools "in the block" that try to combine

SQL, Python, R

And Alteryx is able to blend all three in a single workflow

and is much easier to use

and is much easier to learn

so would love to see a tool that works much like jupyter notebook so that I can write an SQL code quickly test if it works etc.

[Atabarezz]

Also please take a look at TeamSQL, a great tool, an IDE (integrated dev ops environ) for SQL... 

This ability will ease the burden of migrating towards Alteryx from legacy platforms don't you think?

[simonaubert_bd]

Ooooh.

I didn't find that when I post my idea.

As of today, you can pass SQL from :
-input tool

-output tool

-connect in-db

The user interface is very limited, the kind of query you can pass also, welll, not very user-friendly. This generates a lot of frustration among users.

What do I suggest :
1/ A direct button "Query Builder" without having to open a new wf and drop an input box and then go with the presql tool and fight to build a query.

2/ Basically the same features than Dbeaver (https://dbeaver.io ) or DBvisualizer (https://www.dbvis.com/) or SQUIRREL http://squirrel-sql.sourceforge.net/ ):
  -Ability to pass any SQL Code I want (such as update, create, truncate, etc...) when I come from the button, "protected" sql when I am in a workflow
  -autocompletion

  -color coding (The idea is not new )

 
3/ A box "Free Sql Query" that I can branch on a indb or standard wf to pass any SQL query. The Output would be the same as input, just like it would be with a block until done.

[SrucBI]

Upvote -

Also would like to see much more flexible ability to input SQL Commands without having to stick to a rigid "expectation" of what a SQL command looks like. 

eg let's say I want to pull in the dataserver name for some reason and want to output something like

select convert(varchar(30), getdate() ,113))+ @@servername as mystring

- you can't even run this because "error opening select * from " no columns returned

You can't even write a CTE to create a "fake" table:

;with dual as(
select null as dummy
)
select convert(varchar(30), getdate() ,113))+ @@servername as mystring
from dual

-- also fails

So in order to do this, I need to come up with a random table name which I redundantly select from, adding load to the dataserver - it feels very restrictive and I resent having to find workarounds to deal with these gaps in Alteryx functionality.

[j4jackycheng]

Is this idea still in "under review" status after 5 years?  

[KylieF]

Hi @j4jackycheng,

This idea was placed in the Under Review status back in January of 2018, while I understand 2 years is still a long time this idea also falls into a group of ideas the our product team is currently re-evaluating to provide updated statuses to. We appreciate your patience while we work through that batch of ideas and insure an accurate update can be provided.

[DKR]

This would be very helpful to have.

Case:

I have a workflow that is copying from one database into a set of staging tables within another.  Once the data has been copied, I need to run a series of Stored Procedures in a specific order to do further matching within the database.

Being able to have the workflow complete by kicking off each stored procedure would be extremely helpful.

[Michael_Draper]

Another upvote for this functionality. As someone trying to "win over" teammates from legacy systems onto Alteryx, some more flexible options to send *any* SQL code to a target server would be a game-changer. Workarounds completely hamstring the entire strength of the program. Anything that can run SQL commands in-DB and allows the user to control when it happens (eg after some tools but before others) would be an incredible value-add. Can provide more concrete use cases if that's helpful.

A basic one is having to choose between "Create Table" and "Overwrite Table" - depending on our monthly processes we might need one or the other. Using a DROP TABLE IF EXISTS command fixes this immediately, but do not want this in a Pre-SQL statement that runs whenever I interact with the workflow, or Post-SQL with dummy output.

But that is just one example of many that will keep appearing. Please give us the ability to help ourselves while more functionality is added into the base tools!

[YEM]

This is really needed.  I use the post-sql to run stored procedures all the time.  It'd be much easier if I can just fire off sql commands directly.

[Josh_WI]

Hello, I developed a workaround to this issue. Use a placeholder query that every user should have access to (information tables, select none from it, but use presql to run any desired sql (stored procs, table creation, index creation, etc) 

Inside Macro: Presql should replace 'SELECT NULL'

Outside Macro: 

Has been working well for our Data Lake, where table creation with indexes, temporal tables are all built using this component. Probably our #1 used component across our workflows. We also have a version that waits until the statement is complete so that another call can be done in a component after. Hope this helps somebody.