Welcome to the Alteryx Knowledge Base

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: 

 

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:

 

 

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.