Export query results to files

Export query results to files#

This tutorial walks you through exporting the result of a SQL query to files with CREATE EXTERNAL TABLE ... AS SELECT (CETAS). By the end you will have written a query’s rows straight to a CSV file through a controlled data source connection — no intermediate table and no separate copy step.

CETAS is the standard, SQL Server–compatible way to export inside a T-SQL workflow, so the same script runs in a Querona job, a scheduled task, or an ad-hoc session.

Important

File export is disabled by default on a Querona instance. An administrator must enable it (the allow file export setting in the engine configuration) before any CREATE EXTERNAL TABLE ... AS SELECT can write files. Until it is enabled, the statement fails with a clear “export to files … is disabled on this instance” message.

What you’ll use#

  • A running Querona instance (see Installation if you need one).

  • A folder or file share the Querona service account can write to — for example D:\exports\sales or \\fileserver\exports.

  • Permission to create a table in a database, and control of the data source connection. Both are granted by an administrator (see Access rights); the section Who can export below explains exactly which rights apply.

Note

Run the example in an ordinary user database, not the system master database. A CETAS export is authorized like creating a table (it requires table-creation rights), and master does not allow that. The section What CETAS does — and doesn’t — leave behind explains the model.

Step 1 — Create the data source connection#

An export writes through a data source connection — the named, admin-provisioned write target. It is the security boundary for every export: it fixes the base location, and exports may only write inside it.

Create it in the Querona management portal as a file connection named SalesExport whose location is the folder you want to export into (see Create a connection). The location can be a local path, a UNC share, or a file:// URI — for example file:///d:/exports/sales.

Note

Because the connection is the boundary, it stays under administrator control — it carries the location (and, in future, credentials), so it is provisioned once and then granted to the users or roles allowed to export through it.

Step 2 — Choose a file format#

Querona ships built-in external file formats, so there is nothing to register first — just reference one by name:

Name

Format

Notes

csv

Delimited text

Comma-separated, with a header row. Used in this tutorial.

tsv

Delimited text

Tab-separated, with a header row.

parquet

Columnar

Planned; delimited-text formats are available today.

You can list them at any time:

SELECT name, format_type, field_terminator
FROM   sys.external_file_formats;

Step 3 — Export a query with CETAS#

Now export a query. The WITH clause names where the file goes (LOCATION inside the DATA_SOURCE) and how it is written (FILE_FORMAT); the AS SELECT is the data:

CREATE EXTERNAL TABLE dbo.TopCustomers
WITH (
    LOCATION    = 'top-customers.csv',
    DATA_SOURCE = SalesExport,
    FILE_FORMAT = csv
)
AS
SELECT customer_id, name, total_spent
FROM   sales.customers
WHERE  total_spent > 10000
ORDER BY total_spent DESC;

Querona runs the SELECT and streams its rows directly to the file — the data is never copied into a cache or temporary table first. The result is a file at the composed location, here D:\exports\sales\top-customers.csv:

customer_id,name,total_spent
42,"Acme, Inc.",125000
17,Globex,98000
8,Initech,54250

Delimited-text output follows the usual conventions: a header row of column names, values quoted only when they contain the delimiter, a quote, or a line break (RFC 4180), and NULL written as an empty field.

Step 4 — Any SELECT works#

The source is a full, first-class SELECT — joins, aggregation, TOP, expressions and parameters all apply. Scope the data exactly as you want it exported:

CREATE EXTERNAL TABLE dbo.SalesByRegion
WITH (LOCATION = 'sales-by-region.csv', DATA_SOURCE = SalesExport, FILE_FORMAT = csv)
AS
SELECT r.region_name,
       COUNT(*)          AS orders,
       SUM(o.amount)     AS revenue
FROM   sales.orders   AS o
JOIN   sales.regions  AS r ON r.region_id = o.region_id
WHERE  o.order_date >= '2026-01-01'
GROUP BY r.region_name;

Because the export is just a query, you can keep it under source control and re-run it from a job or schedule like any other script.

Step 5 — Organize output into subfolders#

LOCATION may include subfolders, interpreted relative to the data source. Querona creates them under the data-source root as needed — useful for date- or partition-style layouts:

CREATE EXTERNAL TABLE dbo.Q2Customers
WITH (LOCATION = 'year=2026/q2/top-customers.csv', DATA_SOURCE = SalesExport, FILE_FORMAT = csv)
AS SELECT customer_id, name, total_spent FROM sales.customers;

This writes D:\exports\sales\year=2026\q2\top-customers.csv. Using a fresh or date-stamped LOCATION per run is also the simplest way to keep successive exports side by side.

What CETAS does — and doesn’t — leave behind#

A CETAS export does two things, and it helps to keep them apart:

  • It is authorized like a table creation. CREATE EXTERNAL TABLE is a table-creating DDL form, so it requires table-creation rights in the target database and schema — which is why you run it in a user database, not master.

  • It writes the query’s rows to files through the data source connection.

What it does not do, in this release, is leave a queryable external-table object behind: the result is the exported files, not a catalog entry, so sys.external_tables does not list it. You consume the data straight from the files (or simply re-run the query when you need it again).

The database and the data source stay independent — the database is only the authorization scope for the statement, while the data source is the egress for the bytes. A database does not need to be related to, or backed by, the data source you write to; you can run the export from any user database while the rows land in any data source you are allowed to use.

Who can export#

To run a CETAS export you need both:

  • the right to create a table in the target database and schema — the same right a plain CREATE TABLE requires; and

  • control of the data source connection you are writing through.

Both are off by default for ordinary users; an administrator grants them to the users or roles that should be allowed to export, and to the specific data sources they may use. This mirrors SQL Server, where CETAS requires table-creation rights plus the (highly privileged) right over the external data source. See Access rights.

Safe by design — the data source is the boundary#

A LOCATION is always interpreted relative to its data source, and an export can never write outside that data source’s location. Querona rejects, before opening any file, a LOCATION that tries to escape — for example a parent-directory segment, an absolute drive path, a UNC path, or an embedded URL:

CREATE EXTERNAL TABLE dbo.Bad
WITH (LOCATION = '../../secrets.csv', DATA_SOURCE = SalesExport, FILE_FORMAT = csv)
AS SELECT 1 AS id;
-- Error: CREATE EXTERNAL TABLE LOCATION '../../secrets.csv' is not allowed because it
--        contains a parent-directory ('..') segment.

So once an administrator points a data source at a folder, every export through it is confined to that folder and its subfolders.

Note

The files are written by the |Product| service account, so that account also needs operating system write permission to the data source’s location.

Find your data sources and formats#

The external objects are visible through the system catalog views:

SELECT name, location, type_desc FROM sys.external_data_sources;
SELECT name, format_type        FROM sys.external_file_formats;

Next steps#

  • CSV — read delimited files back into Querona as a data source.

  • Access rights — grant and manage the rights an export needs.