How do I…

Short answers to everyday tasks, each with what you will accomplish, the commands to type, an example you can reproduce and what to watch out for. Every recipe links to the commands it uses, and each command page links back.

Looking for a message you just got? See Troubleshooting. Looking for one command's exact syntax? See the command reference or the cheat sheet.

About the examples

Every example runs on WORLD, the sample database included in BroadSQL: countries, cities, languages, currencies and regions, in an H2 database opened with CONNECT WORLD; (see Getting started). You can reproduce each one on your own installation, with the same tables, the same columns and the same values.

A few recipes compare environments. They use three connections that do not exist by default: WORLD-IN (integration) and WORLD-QA (quality assurance), two copies of the sample, and WORLD itself, playing the production database. Set up QA and production copies of WORLD creates them in a few minutes; each of those recipes also says what you can do with WORLD alone.

When an example writes data, it writes into a table, a file or a database you create for the purpose, or into a practice copy of the sample, and it says how to remove or undo what it changed. One recipe, Verify that a data fix changed only the intended rows, changes rows of WORLD itself on the practice set, and puts them back exactly at the end. The sample is ordinary data: to get the original back at any time, replace samples/WorldDB.mv.db with the file of the BroadSQL download while BroadSQL is closed.

Investigate a database

Map a database you do not know, check what depends on a table, watch a value change, find the data that is wrong.

• How do I explore an unfamiliar database in fifteen minutes?SHOW DBINFOS;, SHOW TABLES;, CNT, DESCR, FIND COLUMN, SHOW INDEXES.
• How do I check what depends on a table before I change it?SHOW REFERENCES COUNTRY;, then FIND COLUMN COUNTRY%CODE;
• How do I watch a value every 30 seconds and keep a timeline?REPEAT EVERY 30s;, or add TO world-monitor.csv AS CSV.
• How do I find orphan rows, duplicates and missing values?LEFT JOIN ... WHERE <parent key> IS NULL, HAVING COUNT(*) > 1, COUNT(<column>).

Use lists of values in a query

Values from the clipboard or a colleague's spreadsheet, without retyping them.

• How do I use a list of values from the clipboard in a SQL query?Copy them, then write WHERE CODE IN <@clipboard>.
• How do I check a colleague's spreadsheet list against the database?IN <@excel:review.xlsx:Countries:COUNTRY_CODE>, then LOAD and a LEFT JOIN.

Query and compare across environments

Practice copies of WORLD, the same check in QA and in production, the same table in two databases, a smoke check after each deployment.

• How do I set up QA and production copies of WORLD to practise environment commands?Copy the sample file, add WORLD-IN and WORLD-QA, make WORLD the PROD connection.
• How do I check a figure in QA, then in production, without switching?Run the query in QA, then type / PROD.
• How do I check that a deployment changed a table the same way in QA and production?COMPARE TABLE STRUCTURE CITY WITH WORLD;, then SHOW INDEXES CITY; on each side.
• How do I find the rows that differ between two databases?DUMP both sides AS H2 into one local database, then join them.
• How do I run a post-deployment smoke check on every environment?LET check = SELECT 'OK' FROM ... HAVING ...; after ON ERROR STOP;, then @ after ENV.

Export and share results

Files, spreadsheets and local databases, with one command written the same way every time.

• How do I export tables and query results with DUMP? A step-by-step tutorialDUMP <source> TO <destination> AS <format> [MODE <mode>];
• How do I build a weekly Excel report with one tab per query and a tab that grows?DUMP (<query>) TO report.TAB AS XLSX;, later MODE APPEND.

Load data and keep copies in sync

From a CSV file into a table, and from a remote table into a local copy.

• How do I keep a local copy of a production table up to date?DUMP COUNTRY TO WORKCOPY.COUNTRY AS H2 MODE MERGE KEY(CODE);
• How do I load a CSV file prepared in Excel into a table, with a preview first?LOAD MY_VISIT my_visits.csv PREVIEW; then without PREVIEW.

Automate with Scripts

Save a query once with checked parameters, find it again, make a report Script stop at the first failure.

• How do I save a query with parameters and run it by name?Save it under scripts/, declare -- @param, then @countries/largest.sql continent='Europe';
• How do I organize my saved queries so that I can find them again?Add @description and @tags, then LIB FIND <word>;
• How do I write a report Script that stops at the first failure?ON ERROR STOP;, then DUMP LIB <script> TO <file> AS XLSX;

Keep a record

Work a ticket from the first ID to the answer, and show that a data fix changed only what it should.

• How do I investigate a support ticket and keep the evidence?SET TRACE;, IN <@clipboard>, IN <@last:CODE>, then DUMP / TO ticket AS MD;
• How do I verify that a data fix changed only the intended rows?DUMP ... AS H2 before and after, then compare the two copies on their key.

Not here?

• Connect to your database: Oracle, SQL Server, MySQL and the others.
• Scripts Gallery: ready-made Scripts to download.
• Levels of BroadSQL: what to learn next, one step at a time.
• Found a gap? Tell us which task you were trying to do.

Found something wrong or unclear on this page? Tell us, with the page address.