Overwriting date in pre sql

Hi,

I want update date in pre sql automatically without any manual work.

example :

select * into xxx.mmmm_2023_06_12 from xxx.mmmm

if i run my workflow tomorrow in pre sql it should change the date automatically to xxx.mmmm_2023_07_12

note : i want to load data into table parllaly before loading the data into table it should take backup of that table with today’s date

any solution for it

Output

Accepted answers

All comments

NeoInfiniTech

Hello @Ramcharan99, you need to use the interface tools and turn your workflow into an analytic app to achieve this.

Attached is an analytic app you can open for editing from within Designer (or drag and drop the file to the canvas). You might need to arrange the interface tools manually (according to the screenshot named Interface Designer Configuration) if you copy and paste them into your workflow.

You can delete the Error Message interface tool if you would like to, it prevents the user from leaving the Pre-SQL statement blank if they choose to enter a Pre-SQL statement manually.

You will need to connect the Action tool to the Input Data tool which connects to the database, then select the parameter specified in the attached screenshot named Pre-SQL (it might be selected automatically but please make sure it is selected if this isn't the case).

The necessary formula to make the Pre-SQL statement dynamic is in the Action tool configuration and it is written as follows:

IF [#1] = 'True' THEN
"select * into xxx.mmmm_" +
DateTimeFormat(Left(DateTimeNow(),10),"%Y_%d_%m") +
" from xxx.mmmm"
ELSE [#2] ENDIF

Pre-SQL.png

Interface Designer Configuration.png

Dynamic Pre-SQL.yxwz

Ramcharan99

It Didn't Worked for me can you elaborate more easily

NeoInfiniTech

Hello @Ramcharan99, after opening the YXWZ file you downloaded for editing (File > Open Workflow > Browse), you will first need to place the Input Tool which connects to the DB into the workflow (you can copy & paste the Input Tool from your existing workflow).

You might also want to uncheck the Run PreSQL on tool configuration option in the Input Tool options, as the tool will automatically execute the Pre SQL statement you write in the Pre SQL Statement each time you click off the tool (without running the workflow).

After placing the Input Tool, you will need to connect the Action tool in the screenshot to the Input Tool, then ensure that the PreSQL parameter is selected.

You can build the remaining part of the workflow, then save it once you have finished building it. After saving the workflow, you can either click the wand icon (Run as Analytic App) which is next to the Run button or close Alteryx Designer and double-click the analytic app Dynamic Pre-SQL.yxwz, then click Finish to run the workflow. You can click Exit to close it once it has finished running. Be careful not to click Finish again or it will run the workflow for a second time.

Tool Configuration.png

Ramcharan99

Hi Actually my issue is i want use pre sql in Output tool not in Input tool

Because i want to load my data in a table before loading data i want to take backup of that table with current Date

NeoInfiniTech

It should also work fine when you connect an Output Tool as it also has the Pre Create SQL Statement option. I connected an Output Tool to the Action tool and the parameter was automatically recognized. Can you try it with an Output Tool?

Ramcharan99

What will be the input for Output tool it is throwing error that Incoming connection is missing

NeoInfiniTech

I am assuming that you have already built a workflow which processes the data before writing it to the database. You can copy the entire process in this workflow (including your Output Tool), then connect the Action tool in this workflow to the Output tool.

The input for your Output Tool should be the process you have built or the data you want to transfer into the database. It is also important to select the correct Output Options mode (default is Create New Table, but you might want to change it to Append Existing for example, if you want to insert data into a table that already exists).

I am sharing another workflow which includes sample tools to demonstrate a simple process. You need to change the tools as necessary to work with your data, including the Output Tool which should be configured to work with the database you are connecting to (including the credentials, table name and output mode).

Dynamic Pre-SQL with Sample.yxwz

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703