@export - Export query result

Instead of viewing and exporting query results from Result Set grids, you can export the result of one or more queries to a file from a script. For huge results, this may be the preferred choice due to memory constraints.

Introduction

To do a simple export of the result from a query, create a script as:

1. an @export on command,
2. an @export set command,
3. one or more queries,
4. an @export off command.

Here is a basic example:

@export on;
@export set filename="c:\backups\orders.csv";
select * from Orders;
@export off;

The @export set command is used to define how the exported output should be generated. It reads parameters in the following syntax:

@export set parameter1="value" parameter2="value" ...;

The parameter name is case-insensitive, and the value is also case-insensitive for parameters requiring a defined value as per the parameter descriptions below. The supported parameters is based on the format parameter for the output which can be any of:

• format="CSV" (default)
• format="HTML"
• format="XML"
• format="SQL"
• format="TXT"
• format="Excel"
• format="JSON"

Parameters for @export set

The following chapters lists all supported parameters.

Generic parameters for all output formats

These parameters are valid for all output formats.

CSV parameters

HTML parameters

XML parameters

SQL parameters

INSERT INTO TAB (ID, FI, LA) VALUES
(1, 'abc', 'def'),
(2, 'abc', 'def');

--/
BEGIN BATCH
INSERT INTO TAB (ID, FI, LA) VALUES (1, 'abc', 'def');
INSERT INTO TAB (ID, FI, LA) VALUES (2, 'abc', 'def');
APPLY BATCH;
/

INSERT ALL
INTO TAB (ID, FI, LA) VALUES (1, 'abc', 'def')
INTO TAB (ID, FI, LA) VALUES (2, 'abc', 'def')
SELECT 1 FROM dual;

INSERT INTO TAB (ID, FI, LA)
SELECT 1, 'abc', 'def' UNION ALL
SELECT 2, 'abc', 'def';

TXT parameters

Excel parameters

JSON parameters

Here are a few examples using some of these settings.

Automatic table name to file mapping

This example shows how to make the filename the same as the table name in the select statement. The example also shows several select statements. Each will be exported in the SQL format. Since the filename is defined to be automatically set, this means that there will be one file per result set and each file is named by the name of its table.

@export on;
@export set filename="C:\backups\${dbvis-object}$" format="sql";
select * from Orders;
select * from Products;
select * from Transactions;
@export off;

The ${dbvis-object}$ variable is not substituted with a table name if using the AppendFile="true/clear" parameter.

Multiple results to a single file

This example shows how all result sets can be exported to a single file. The AppendFile parameter supports the following values.

• true
  The following result sets will all be exported to a single file
• false
  Turn off the append processing
• clear
  Same as the true value but this will in addition clear the file before the first result set is exported

@export on;
@export set Format="sql" Filename="C:\Backups\alltables.sql" AppendFile="clear";
select * from Orders;
select * from Products;
select * from Transactions;
@export off;

Using predefined settings

If you save settings when exporting a table or a schema, you can use the Settings parameter to reference the settings file.

@export on;
@export set Settings="C:\tmp\htmlsettings.xml" Filename="C:\backups\${dbvis-object}$";
select * from Orders;
select * from Products;
select * from Transactions;
@export off;

Limit the number of exported rows

You can use the @set maxrows command to override the Max Rows field value specified in the SQL Commander tab toolbar.

@set maxrows 10;
@export on;
@export set Format="sql" Filename="c:\Backups\alltables.sql";
select * from Orders;
select * from Products;
select * from Transactions;
@export off;

@set maxrows="-1"; will export all rows.

Other Ways to Export Table Data

• Export all or selected tables with the Export Schema or Export Table assistant
• Export query results by exporting the grid with the query results