Title

sql -- Usq SQL over ODBC

Overall Description

This set of commands if for working with remote odbc/sql databases.

Contents

Command Description

sql path Set the odbc dsn/server, db, schema

sql get Load (part of) one table into Stata

sql put Place the Stata data as a remote table

sql finish Place the Stata metadata on the server

sql move Move or rename a table

sql merge Merge a table into another table

sql write Write a bit of standard sql code (ie, SP)

sqli Execute any sql code, from the command line

sql do Execute sql do script (specially formatted sql), from a file

sql cols Get a selection of column names

sql tables List tables from a schema

sql dbdescription Generate a description of a remote database

sql clear Drop a number of remote tables, and possibly their schema

sql get

Syntax

sql get [sqlpath.]table [, keep(varlist) quick(integer) distinct where(clause)]

sql get [<]

Description

sql get loads part of a remote table into Stata. Both distinct and where(clause) function exactly like they do in sql, but the other options provide some advantages:

keep(varlist) accepts a standard varelist; that is, you can use abbreviations and wildcards instead of exactly spelling out column names, and you can use
ranges if you know the remote table column order.

quick(integer) returns just integer records from the table (quickly). For CDW 'fact' tables, it should return the first integer records from the most recent, complete, quarter (by the main date index).
For everything else, it's sql TOP.

sql get with no parameters will reload the data if the current data source was set with sql get or sqli.
sql get < will re-run the most recent prior query created with sql get or sqli.

If metadata has been placed with sql finish table, it will be retrieved and set up appropriately, so things are labeled, formatted, etc.

sql put

Syntax

sql put [sqlpath.]table [if] [in] [, keep(varelist)]

Description

sql put places the (selected) Stata data on the remote server as table. Any metadata from sql do will also be uploaded.

sql finish

Syntax

sql finish table

Description

sql finish places the everything in the Stata datafile except the data onto the remote server, as a metadata blob. sql get table will retrieve it along with the data,
and create a complete Stata dataset, with labels, formats, etc.

sql move

Syntax

sql move original, to(moved)

Description

sql move moves or renames a table to another schema and/or table in the same database. Extended (sql do) meta-data will be updated as well. A new schema will be created, if necessary.

sql merge

Syntax

sql merge addition, into(master:query-name) [keep]

Description

sql merge merges table addition into table master. master must have a clustered index, and addition must include those index columns.

You must supply a query-name which will identify the meta-data that came from addition (eg, the original sql code and query description).

keep keeps addition in the database as well. If keep is not specified, addition is deleted after being merged into master.

sqli

Syntax

sqli sql code

sqli [<]

Description

sqli submits sql code to the remote server for execution.

sqli cannot definitely identify table names in sql code, but words following " from " or " join " or " table " will be considered table names, and interpreted in light of the sql path.

If the first word in the final statement in sql code is select, sqli will attempt to download the results as Stata data. Otherwise, the code will simply be executed.

sqli with no parameters will reload the data if the current data source was set with sql get or sqli.
sqli < will re-run the most recent prior query created with sql get or sqli.

sql cols

Syntax

sql cols table [columns] [, alias(prefix) path(sqlpath)]

Description

sql cols lists all columns matching columns from the specified table. The table is found in the current sql path, or the sqlpath specified. columns matches like a varelist, treating columns as variables.

The results are listed twice: First as a normal 'paragraph', and then as a single line of comma-separated values.
If alias(prefix) is specified, the second display will prefix each name with the specified table-alias.

The single-line display may be wider than the window, but it will be copy-able anyway, suitable for pasting elsewhere.

sql tables

Syntax

sql tables [tables] [, path(sqlpath)]

Description

sql tables lists all tables matching tables from the current sql path, or the sqlpath specified. tables matches like a varelist, treating tables as variables.

sql dbdescription

Syntax

sql dbdescription [schemas] [, path(sqlpath) saving(filepath) portable]

Description

sql dbd creates an html page with metadata for (part of) one database
using the current sql path or the one specified in path(sqlpath). It includes the extended meta-data created by sql do.
schemas matches like a varelist, treating schemas as variables.

saving(filepath) specifies the filepath for the html output. The default is the project settings directory, and the sqlpath database-name.

portable writes the file so that it does not depend on anything in the Stata directories and will continue to function if, for example, it is emailed to someone else.

All of the details (except portable) are remembered, specific to the project, and will be re-used as defaults in future invocations.

sql clear

Syntax

sql clear tables [, path(sqlpath) schema]

Description

sql clear drops multiple remote tables, and possibly an empty schema.

sql clear will first list all the tables in the relevant schema, highlighting the ones that would be deleted, and asks for confirmation before deleting anything.

The schema is the one from the current sql path, or the sqlpath specified. tables matches like a varelist, treating tables as variables.

Specifying the schema option will drop the schema after dropping the tables, if the schema is empty.