Write an SQL expression to directly query a database. You can load the result set into variables, and then use those variables at another stage in the flow.
SQL Script in Master Flow
SQL Script Properties
The following shows the Properties panel where the SQL Script node is selected on the canvas (blue arrow above):
General
Display Name
The default display name of the node is SQL Script 1. Any subsequent nodes are named according to this naming convention, with the appropriate numeric suffix, for example SQL Script 2, SQL Script 3, and so on.
You can change the SQL script node name from directly from this field.
Description
You have the option to add a description to each node; this can be a useful way of documenting the node for yourself and other users.
Validate
Validate the SQL script.
Sql Script
Server
Choose the server where the relevant database is stored.
Database
Choose the database that you want to query.
SQL Script
You can type or paste your SQL script directly into the script panel or use the SQL Editor or Generate Your Script dialogs to build or generate your script.
SQL Editor
You can open the SQL Editor which exposes the data source's schemas; you can easily add tables and columns to the SQL statement by double-clicking or dragging and dropping. The editor also exposes any variables that have been configured in the Data Flow, enabling you to inject variables into the SQL statement.
Click the SQL icon to open the SQL Editor (brown arrow above).
Generate Your Script
You can generate a script by clicking the Gen AI icon from the Properties panel and, in the Generate Your Script dialog, typing a “prompt” (description of your query) in natural language. Once you are ready, click Run. The AI uses its underlying LLM to generate a script based on your prompt. For more information, see Generate Your Script.
Explain Script
Use the Explain Script function to produce an AI-generated explanation of what the script does (note this is available even if the script was not itself AI-generated). Note: Each time you click the Explain Script button, a new explanation is generated. For more information, see Explain Script.
Commit database changes
In-Memory databases only.
If your SQL Script node manipulates your data structure, for example, by adding or deleting tables, you should select the Commit database changes checkbox to ensure your changes are persisted.
Warning: Depending on the size of your database, this may be a heavy operation. If you have multiple changes of this type (multiple SQL Script nodes), you can minimize the impact of this operation by setting the commit option only on the last node.
Load Result set into Variables
Load the result set (green arrow below) of the SQL script into a variable added using the + icon (yellow arrow below).
You can create a list variable (which are used in For Each Loops) using the union select function to select multiple values.
select 'Sydney' union select 'Brisbane' union select 'Melbourne'
- Click here to learn more about variables.
Example
In the example below, a list variable will be created containing the cities Sydney, Brisbane, and Melbourne:
