Title

sql do -- Execute sql-do script

Syntax

sql do filename [, options]

options

path(sqlpath) a sql path to use for this script only

destination() the destination for the final table

merge() merge the final result into an existing table

only() specify part of the script to execute

not() specify part of the script to exclude

swap() specify values for swappable tags

test include test script

upload(write-path) upload tables to a different server

preview do not execute; preview only

Description

sql do executes specially formatted sql-do script, and either downloads the result or places it remotely.
sql do also saves extended meta-data about the tables it creates.

file-path is optional when the command is executed from the editor, using fromEditor. In that case, the script is read from the editor-file in memory.

Options

path(sqlpath) specifies a sql path to use instead of the current one.

destination(name) specifies where to place the table that results from the script. name will use the sql path to fill in unspecified database & schema.
When destination() is not specified, the final ^t=name in the script will be taken as name. When name is *, the results are downloaded into Stata.

destination() cannot be specified with merge().

merge(table:query-name) merges the final results into table. table must have a clustered index, and the result of the script must include those index columns.

You must supply a query-name which will identify the meta-data that came from this command.

merge() cannot be specified with destination().

only(details) restricts execution to a subset of the script file. The syntax is:

only([wrapper-id:] [name list])

You can specify either wrapper-id or name list, or both.

name list works like a varelist. When specified, only the parts with ^t=name matching name list will be executed.

sql do always executes script with one single wrapper-id. When none is specified in only(), script with NO wrapper-id is executed.

not(name list) excludes the selected tables from execution. name list works as it does in only().

swap(tag(value) [...]) specifies values to use for the tags (swappable tags) in the script.

test will treat certain comments in the script as executable script; specifically:

/*test:executable test code:test*/

upload(write-path) specifies a sql path for uploading tables -- useful if you can't write to the server you're reading from.
write-path must be specified with an explicit server address, rather than a dsn.

You'll need to ensure that write-path is readable from the server where the main query is running.

preview causes the sql do preview to display, without actually executing the script.
The preview shows all the tables and tags included in the script, and those selected for execution, and shows the final values assigned to each tag.

Meta-data

sql do saves some extra meta-data about every table it creates or downloads:

o- A query desciption

o- Execution time

o- The sql script that was executed

o- The local filename where the sql-do script originated

Note that it is the actual, executed, sql script that is saved with the metadata - after all swaps and table-name resolutions.

When results are merged into an existing table, the meta-data from each merge, and the merged columns, are also assigned a query name.

For results saved remotely, the meta-data is stored in the relevant database, and can be viewed with the sql dbdescription command.

For results downloaded to stata, the meta-data is stored as _dta characteristics.
If the data is later uploaded with one of the sql commands, the meta-data will be written the same as if the table had been created with sql do directly.
The local characteristics can be inspected in any of the usual ways.