How to Read and Execute a .SQL file

[CDunhill]

Hello, has anyone managed point Alteryx at a .SQL file and execute the query in it? What approach would you take?

I'm assuming 3 main options?

1. Dynamic input tool (which I've tried and failed with in this case!)
2. Run tool to make use of BCP, perhaps, or some other CMD app
3. Macro

I'd prefer NOT to go down the macro route as, despite my efforts, I'm still struggling with them to be honest. But if that is the only solution I will try to work with it!

Thanks in advance

C

[danilang]

Hi @CDunhill 

I guess this is a follow up from your similar question from yesterday.  

You can't execute just any generic sql in the dynamic input tool.  For instance, you can't have any DDL statements, and the entire set of statements has to return a single cursor.     

Looking at your message in your original post, I'd say that the error is generated by the ODBC driver.   It seems to be having a problem with multi-line format of your tsql. 

A few things to try. 

- Use a semi-colon as the delimiter in your summarize tool.  This might sneak by the driver.

- Convert the combination of Use databasename; Select * from Tablename to Select * from databasename..Tablename.  This will only solve the example that you provided, but at least it's a start

Dan

[CDunhill]

Thanks for this and apologies for the slow reply: I missed your response! Will check it out a little later and mark as solution if it works.

[Matt_D]

@CDunhill I'm not sure what if this is what you're after but I'd do your replace in the formula tool and then replace the whole statement in the dynamic select tool with the new one? A very simple use below but you can get very creative if you wish.

To bring the sql file into Alteryx:

You can concatenate the query but it doesn't need to be new line, space will do

Thanks,

Matt