Hi,
I'm not sure if all of this is possible or not within Alteryx.
A request came in to me today of "Could we use Alteryx to run this SQL Statement (that involves creating temp dbs) and then create an output file?"
The short answer to that was "yes absolutely, thats easy!"
The follow-up I was given is "Great! Could we run any statement we want through this?"
So, I tried to build a batch macro that might allow for something like this - I've attached what I came up with so far.
However, when I try to run this (leveraging preSQL) I get a "No Columns Returned" error on my PreSQL statement.
Any thoughts anyone has would be fantastic
Solved! Go to Solution.
Sorry @Claje, but I'm confused. I do want to help you.
What is going to feed the dynamic nature of the SQL generation (and pre/post SQL statements)?
Have you looked at the dynamic input tool for a solution? https://help.alteryx.com/11.3/index.htm#DynamicInput.htm
With a little more context and information, I might be of help.
Cheers,
Mark
Hi,
I seem to have sorted out my issue, thanks in part to your response causing me to take a closer look at things, although I have a follow-up question for you.
Thank you very much for the quick response!
The actual error I was encountering was because I had accidentally included part of the PRESQL create series in my main SQL query (that was creating a temp table with no output) and so Alteryx was correctly saying "Hey that piece of code didnt return columns", and I was reading the error and going "oh this must have happened in PRESQL" due to the specific SQL that was in the error message.
Lesson learned - make sure to test with a different piece of code before assuming the problem is in Alteryx :)
My follow-up question is: Do you know if the Dynamic Input route will work with Pre-SQL? My understanding is that PreSQL runs before dynamic input updates anything, but I would love to be proven wrong!
Glad to have helped.
In using the dynamic SQL, I wonder if you were to have multiple SQL statements if you could create something like:
DELETE * FROM TABLE;
INSERT .....
SELECT * ...
has anyone responded to this question?
Hi,
As far as I am aware, there is no way to use only the "SQL" portion of an input or dynamic input tool to insert, select, and delete from a data source, due to the way that the drivers are configured in these tools.
If someone has an example of doing this, I am happy to hear it, but I've never encountered a method that doesn't leverage the PreSQL/PostSQL portions of the tools.
The unfortunate side effect of this is that the Dynamic Input tool cannot be used alone to modify PreSQL/PostSQL on a per query basis, so it will not work for executing arbitrary sql.
I have tested the example MarqueeCrew suggested, and encounter the error :
Error: Input Data (1): Error: No target OLEDB object available. in Query: INSERT INTO [dbo].TestTable VALUES(1,'b','c');SELECT * FROM [dbo].TestTable
Further, In testing this, I found that the main SQL statement WILL accept a "CREATE TABLE" statement, but I would not recommend using this functionality, since it will attempt to create this table every time you add a tool to your workflow, unless you specifically check the User setting "Disable Auto Configure".