Dynamic parameters in post create SQL /pre create SQL

[citronmelisa]

Hi! 
This ticket existed before as an idea, but since it is not implemented, I was wondering if someone could advise me on a workaround.

My use case: renaming tables with table names passed for a macro through the text box. 

So what I would want to do -- update my existing post SQL which could look something like this:

alter table TABLE_NAME
rename to TABLE_NAME_OLD;
alter table TABLE_NAME_NEW
rename to TABLE_NAME;

With a value for TABLE_NAME that is passed through text box. 

Any help would be appreciated!

[Sebastiaandb · Accepted solution]

Hi @citronmelisa ,

I have two alternatives for you that might work. In my example, i used a input data tool but i'm sure this will work with a output data tool as wel.

Option 1 - Text input tool

Just make a macro like i did with a text box and action tool connected to the input data tool. 

In the action tool you can select the post sql statement if i'm right. Once you added the macro to your workflow, you can fill in the text box and it will alter the table name by the name you entered.

Option 2 - Parameter tool

Basically with the same setup but replacing the text input tool by the parameter tool, you can use a value from f.e. a text input tool or incoming data as input for a batch macro. The macro will then reply the table within the post sql.

Unfortunately, i can't send you my example as i'm using company connections to test the setup. 

Hope it helps.

Greetings,

Seb

[citronmelisa]

Hi, @Sebastiaandb!

Thank you, this seems like a good solution! 
For my use case works, as we can replace specific text and keep different suffixes. Makes me wonder though -- If I needed more than one paramater in the post/pre SQL, how would that be approached.

All the best, 
Nora

[Sebastiaandb]

Hi @citronmelisa!

I'm not sure but my best guess is that you can just add more text input tools/parameter tools to the macro and attach them to the output data tool. Let them replace the right string in the post SQL statement and you should be fine to use more parameters !

Greetings,

Seb