BroadSQL BroadSQL Downloads
Documentation / Patterns & List Sources

Patterns & List Sources

BroadSQL recognizes a handful of special macro syntaxes directly inside a SQL query, substituted before the query runs. They fall into two groups: list sources, which expand to a quoted, comma-separated list of values (typically after IN), and execution shortcuts, which run a query instead of typing it out again.

List sources

Each of these is written as <...> at the point in your SQL where the list belongs, e.g. WHERE CUSTOMER_ID IN <@last:CUSTOMER_ID>. A source that resolves to zero values fails with a clear error rather than silently producing an empty IN ().

<@fileName>

Reads fileName line by line: each line is trimmed, blank lines are skipped. This is the original list-source syntax.

Example: select count(*) from TEST where id in <@c:\codes.txt>

<@csv:<path>:<column>>

Reads one named column from a CSV file, matched by header name (exact match first, then case-insensitive; an ambiguous/duplicate header is rejected). Blank cells in the column are skipped. Parsed with real RFC 4180 parsing (quoted values, embedded separators/quotes). Read as UTF-8; a leading byte-order mark on the header row is stripped automatically.

Example: <@csv:c:\temp\customers.csv:CUSTOMER_ID>

<@excel:<path>:<sheet>:<column>>

Reads one named column, on one named sheet, from an .xlsx workbook (sheet and column both matched by name, exact then case-insensitive, never by letter/position, so reordering columns doesn't change which values are used). Numeric cells render as a plain integer or exact decimal (no thousands separators); a date-formatted numeric cell renders as its ISO date/time; a formula cell uses its cached result. The legacy .xls binary format is not supported.

Example: <@excel:c:\temp\customers.xlsx:Customers:CUSTOMER_ID>

<@last:<column>>

Reuses one column of the most recently successfully displayed SQL query result: the "rerun a follow-up query against what I just looked at" pattern. Matched by the result's visible column label (so a query alias, e.g. SELECT customer_id AS cid, resolves by cid), exact match first, then case-insensitive; a label matching more than once is rejected as ambiguous. SQL NULL values in the column are skipped, never rendered as the literal text NULL. Refuses to resolve at all (rather than silently truncating) once the underlying result exceeded the safety row limit.

Example:

SELECT CUSTOMER_ID FROM COMPLAINT WHERE STATUS = 'OPEN';
SELECT * FROM ORDERS WHERE CUSTOMER_ID IN <@last:CUSTOMER_ID>;

<@clipboard>

Takes the values currently on the system clipboard, one per line: the "copy one column from Excel/Calc, paste straight into a query" workflow. Read as plain text, split on any line ending; each line trimmed, blank lines skipped. If a copied line contains a tab character (more than one column copied together), this fails clearly rather than guessing which column to use; v1 is single-column only. An empty or non-text clipboard produces an empty list (which then fails the same "zero values" error as any other source).

Example: select * from orders where customer_id in <@clipboard>

Execution shortcuts

@<fileName>

Runs the SQL commands stored in a file. A relative fileName resolves against the folder BroadSQL was started from, so @scripts\myfile.sql; runs a script from the scripts folder shipped with the install. The same script can also be run by name with SCRIPT RUN, which additionally applies Database Group/environment scoping and %N parameter substitution.

Example: @c:\dbRestore.sql;

/

Re-runs the last SQL statement, exactly as it was last executed.

/ <environment>

Re-runs the last SQL statement transiently against another environment's connection instead of the current one: handy for re-checking a query you just ran in one environment (e.g. QA) against another (e.g. PROD) without switching connections. The active connection and SHOW QUERY's stored text are left untouched; only that one run targets the other environment.

Example: / QA

Examples

SELECT CUSTOMER_ID FROM COMPLAINT WHERE STATUS = 'OPEN';
SELECT * FROM ORDERS WHERE CUSTOMER_ID IN <@last:CUSTOMER_ID>;

CONNECT MADBPROD;
SELECT COUNT(*) FROM ORDERS WHERE STATUS = 'FAILED';
/ QA;
  • Command reference: every real BroadSQL command; list sources and execution shortcuts are macro syntax, not commands of their own, so they aren't listed there.
  • User guide
  • Export: what to do with a query's results once you have them.