Using variables for tables in SQL editor

[mikucera]

I would like to use an sql statement to load data to Alteryx that contains a variable for a table, e.g.:

declare @table_name as varchar(128)
set @table_name='my_table'
EXEC ('SELECT * from ' @table_name)

However Alteryx doesn't read the data in this way. Is it possible to do this somehow?

[mceleavey]

Hi @mikucera ,

You can put this is in a macro and feed in the table value as a parameter, either generated by logic, or selected via an interface tool (text input, dropdown etc.) but is there any reason you're using a SQL statement rather than just using Alteryx?

Can you give us a bit more info?

M.

[mikucera]

Hi @mceleavey ,

the sql query that I'd like to use to load the data is quite long with many joins and I found using joins in Alteryx not very user friendly so inputing this as a sql code would be much easier in the context of all the remaining parts of the whole workflow that will consist of other data inputs. 

I will explore the alternative options, or I'll just use the sql code without the variable.

Many thanks,

Michal

[mceleavey]

Hi @mikucera ,

that's really interesting that you find SQL joins more user friendly than Alteryx! I've never heard it that way around before!

With Alteryx there's no coding and you can literally take all possible outcomes (left, right, inner, left outer etc.) simultaneously from one tool.

I'm guessing you haven't been using Alteryx long and have become accustomed to using SQL?

M.

[mikucera]

Yes, you are right that I haven't been using Alteryx very long time and it's just my very first impression that e.g. the left/right join (left/right in SQL language) is bit "clumsy" while you have to include the UNION..  But thanks again, I will explore the options.

Michal

[mceleavey · Accepted solution]

Hi @mikucera ,

Yes, the coding in SQL is really clumsy compared to Alteryx. You can simply connect your data source, select the table or tables you want and you can then join them in the visual query builder:

Or pull them into Alteryx and use the simple drag and drop tools:

M.

[Mitch_S]

I too would love to be able to figure out how to use DECLARE and SET in Alteryx's SQL Editor. I always prefer command lines over GUIs and find SQL much more efficient for coding, then I just use Alteryx to automate the extraction and distribution of the data. 

[Nathan_S]

Is there an answer to this that Alteryx can or cannot read the SET and DECLAR parts of a SQL query? I know the functionality can be done via macros/dynamic input, etc but I have some users that are new to Alteryx, have variable declarations in their SQL queries, and I would prefer that they modify their original queries as little as possible if necessary