Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop experts.

Alteryx Pre/Post SQL Statements

DiganP
Alteryx Alumni (Retired)
Created

Alteryx has the ability to create and query Pre and Post SQL statements. Using this feature is good if you need to create a temporary table, delete or update the table before running the select. With this, you also have the ability to run stored procedures (via exec stored_procedure;) before and after the actual query as well.

Order of operations:

The Pre-SQL statement would run first, then the Table or Query and finally the Post-SQL statement. One thing to note is that Alteryx will not output the results from the Pre/Post SQL statements into the workflow.

Let’s look at an example below:Overall.PNG

Pre-SQL:

Drop table [dbo].[Alot_of_data_1] CREATE TABLE [dbo].[Alot_of_data_1]( [Store Num] [varchar](5) NULL, [Address] [varchar](25) NULL, [City] [varchar](25) NULL, [State] [varchar](2) NULL, [ZIP] [varchar](5) NULL, [Lat] [float] NULL, [Lon] [float] NULL, [County ] [varchar](64) NULL, [Region] [char](5) NULL, [Type] [char](4) NULL, [Date] Datetime ) ON [PRIMARY];  WAITFOR DELAY '00:00:05'; 

Table or SQL:

Select * From Alot_of_data_1; WAITFOR DELAY '00:00:05';

Post-SQL:

INSERT INTO [dbo].[Alot_of_data_1] ([Store Num],[Address],[City],[State],[ZIP],[Lat],[Lon],[County ],[Region],[Type],[Date]) VALUES('1234','1234 irvine drive','Irvine','CA','92627',-85.3,105,'Orange','CA','XY',CURRENT_TIMESTAMP); 

What this configuration does:

  1. Table Alot_of_data_1 is dropped then created with the specified columns. Delay for 5 seconds.
  2. Empty table (Alot_of_data_1) is read into Alteryx Designer. Delay for 5 seconds.
  3. Row is inserted into table (Alot_of_data_1) as specified with the timestamp.

Looking at the logs:

SQL Profiler.PNG

The Workflow Started at 11:04:10

Pre-SQL Timestamp: 11:04:10

SQL Query Timestamp: 11:04:15

Post-SQL Timestamp: 11:04:20

Since there’s a 5 second delay, the timestamps are 5 seconds off to the next SQL statement.

Note:

Pre-SQL statements will run when the workflow is opened, when a user clicks on the canvas or new tools are added. This can cause your Pre-SQL statements to run which may cause workflows to fail or unnecessary queries to run.

To prevent Pre-SQL from running when opening the workflow or clicking on the canvas:

Within Alteryx Designer > Under Options > User Settings > Edit User Settings there is an option in the "Advanced" tab called "Disable Auto Configure". If you check that off it will prevent your Pre-SQL from running. However, it also prevents metadata from passing automatically through the workflow, so some tools will get harder to use (for example the Summarize Tool).

Another fix is to add precautions in your SQL Statement.For example, if you have a DROP statement in the Pre-SQL query then you may want to configure your SQL with something along the lines of :

IF EXISTS(SELECT TOP 1 * FROM TABLENAME) DROP TABLE TABLENAME;

Or something like:

IF OBJECT_ID('dbo.Tablename', 'U') IS NOT NULL  DROP TABLE dbo.Tablename;  

This would check if the table exists before dropping it, which should remove any unnecessary queries to run.

Comments
TommyB
6 - Meteoroid

DiganP - to claryify, in order to not execute the Pre-SQL when the workflow is opened, should the "Disable Auto Configure" box be checked or not-checked?

 

It appears that this "Disable Auto Configure" option is by default not-checked for me, and I don't want Pre-SQL executed automatically. So I think I'm good to go, right?

 

On a side note, I think that the automatic execution of Pre-SQL upon opening workflow is undesired behavior. Nothing about the workflow should run until I hit "Run Workflow". This should be fixed in my opinion.

TommyB
6 - Meteoroid

Is a semi-colon needed when invoking a stored procedure in SQLServer?

 

When I don't include a semi-colon, there is no error returned in Alteryx Designer so it gives the impression that the procedure ran to completion.

 

Is there a best-practice recommendation for returning the success status or result of the stored procedure back to Alteryx?

DiganP
Alteryx Alumni (Retired)

@TommyB 

1. In order to not execute the Pre-SQL when the workflow is opened, should the "Disable Auto Configure" box be checked or not-checked?

  • The "Disable Auto Configure" should be checked off. This will prevent you from running the Pre-SQL executed automatically.

2. As a best practice, Stored Procedures should have a semi-colon. Also, stored procedures should show up in the Stored Procedure tab in the Visual Query Builder. The results in the results tab should have the results from the stored procedure after it is ran. 

matrongone
5 - Atom

Im having an issue running preSQL statement

Im trying to delete the last 5 records in a Post gres table

 

Delete Call_Coding_Summary_MT
From Call_Coding_Summary_MT
WHERE REPORT_DATE between (CURRENT_DATE-5) and CURRENT_DATE

 

I get an error syntax error at or near "Call_Coding_Summary_MT"

MuralidharAreti
8 - Asteroid

Is it possible to pull a field from the incoming data in the pre-sql statement?

 

e.g. 

UPDATE dbo.Table
SET column = NULL
WHERE FieldId IN (1,2,3)

 

Where I would replace the 1,2,3 with a value from the dataset. 

 

Also I agree fully with this statement from @TommyB: 

On a side note, I think that the automatic execution of Pre-SQL upon opening workflow is undesired behavior. Nothing about the workflow should run until I hit "Run Workflow". This should be fixed in my opinion.

Edit/Follow-up question:

Will disabling tools that write to output help prevent the auto run pre-sql thing?

HiralMavani
7 - Meteor

How do we write Post Create SQL Statement in Write In-DB tools? I cant see that option. 

rajamahajan85
7 - Meteor

It doesn't work when I use Post SQL to insert data from the same query result that's in the Table or Query. It only execute the Select query but not PostSQL.

CatheyH
8 - Asteroid

@DiganP wrote:

Note:

Pre-SQL statements will run when the workflow is opened, when a user clicks on the canvas or new tools are added. This can cause your Pre-SQL statements to run which may cause workflows to fail or unnecessary queries to run.

 

To prevent Pre-SQL from running when opening the workflow or clicking on the canvas:

Within Alteryx Designer > Under Options > User Settings > Edit User Settings there is an option in the "Advanced" tab called "Disable Auto Configure". If you check that off it will prevent your Pre-SQL from running. However, it also prevents metadata from passing automatically through the workflow, so some tools will get harder to use (for example the Summarize Tool).

Turning this off is a major issue when configuring a large workflow as it prevents the workflow from passing variables into the next tool to allow you to check / configure them.

For input tools an easier solution is to uncheck the "Run Pre-SQL on tool configuration" which is a tool-level rather than user-level option that I found by accident today. 

CatheyH_0-1635864847281.png

 

 

RRaoDannayak
7 - Meteor

Hi All,

 

@DiganP I tired to insert the data into SQL Server tables with a Loop statement for 500 random record numbers, It works perfectly fine with SQL Server but when i applied the same query from Pre SQL Statement I can only insert up to 144 records Max, Is there a limitations to insert the number of records into SQL Sever with Alteryx ??

 

Here is the code I used 

 


DECLARE @first AS INT = 1
DECLARE @last AS INT = 500
 
WHILE(@first <= @last)
Begin 
insert into Dummy table(Customer_nbr,Account_title,acct_assn_nbr,acct_appl_code,acct_cfm,update_type)
values(RAND() * 100000, 'Dummy checking',RAND() * 10,'PK',RAND() * 10000,1)
   SET @first += 1
END

suppahc
7 - Meteor

Are Pre SQL statements run within SQL prior to be pulled into alteryx? For example, if you had data going back to 1900, but you knew you only wanted data for 2021, could you filter the dataset (which would happen in the SQL environment) and then pull it into Alteryx. The reason I ask is that pulling all the data first before filtering it can take much longer than if I'm able to execute statements in the SQL environment before extracting the data.

RRaoDannayak
7 - Meteor

@suppahc  As per my understanding you want to pull the data for only 2021 from SQL so, in that case why cant you restrict the data from the Input tool ?? if you click under "Table or query" for your specific table you can navigate to the "SQL Editor" where you can edit your desired query and restrict the data before you even import it to Alteryx

 

RRaoDannayak_0-1653414451126.pngRRaoDannayak_1-1653414496235.png

 

suppahc
7 - Meteor

Where is the filtering happening in that statement? Does it pull in all data into Alteryx, and then filter the results within Alteryx? Or does it filter it before pulling in all records. 

RRaoDannayak
7 - Meteor

@suppahc  The Filtering happens before the Pull that is one of the reasons I suggested you to Filter the data through SQL Editor that way you don't need to pull the data from 1900 till 2021 but only get the 2021 data

lepome
Alteryx Alumni (Retired)

Yes, don't use a pre-SQL statement.  Just use a query as the pull itself.

simonaubert_bd
13 - Pulsar

Hello,

Personnaly, I use pre and post SQL statement to pass "free" sql through macro :p

 

By the way, this nice feature is still not available for in-db despite this popular idea.... under review for years
https://community.alteryx.com/t5/Alteryx-Designer-Ideas/Pre-SQL-for-InDB/idi-p/94203

 

Best regards

@muralidhar, were you able to get it to work to use data from workflow in pre sql ?