Multiple SQL txt file to be read and run in a batch macro

Question

Hi Alteryx

I need suggestion for my case, I have multiple SQL files that needs to be read in our data lake here's what I want to achieve

1. I want to read SQL files stores in a single directory

2. In that same directory, I want a macro that will read each SQL

3. Here's the part which I'm currently stuck, I'm not really sure on how it will update the sql fields in the input, read and run each sql script and union everything in one yxdb.

Appreciate your advise, thanks!

rzdodson · Accepted Answer

@RChan19 what elements in the SQL query are you attempting to change? And, are those elements being applied to all of the SQL queries within the directory, or just a select few?

For instance, if there is a date range you are attempting to change out, you can actually utilize a batch macro to change out that element specifically. You'll delete every portion of your SQL query that is not that element to tell Alteryx that you only want to change out that date range. Once you step through each SQL query, the results will be unioned together.

If you are changing specific elements to only a few of the queries, I would actually recommend running a nested batch macro in this use case so you can have a mechanism to handle the change exceptions. The first layer will establish the directory in Alteryx while also creating new fields to handle the exceptions you need to apply. Then, the second/inner most layer would batch through each SQL query. After it runs, it'll union the results similar to the suggestion above.

Hope that helps navigate you a bit. Feel free to reach out with any questions you have. :)

apathetichell · Accepted Answer

Are the files in your directory actual SQL - or are you trying to modify an existing query? Assuming the former - use dynamic input in-db. create a text input wth a query and a connection in row 1. use dynamic input in db. use a control parameter/action tool to turn this into a macro. in an outer workflow - read each sql file in as text. use a summarize tool with /n - concatenate to make sure they are one line (if needed) attach your macro. map your query to your control parameter.