DuckDB’s CLI provides a convenient way to generate INSERT statements from query results. This can be particularly useful for data migration, creating test data, or backing up specific data subsets. Here’s how to do it.

Checking the Current Output Mode

First, check your current output mode in the DuckDB CLI using the .mode command:

.mode

This will show your current output mode, which is typically duckbox by default.

Here’s what mine returns:

current output mode: table

I had previously switched to table mode, and so that’s my current mode.

Here’s an example of what happens when I run a query:

SELECT * FROM employees;

Output:

+----+---------------+-------------+-----------+
| id |     name      | department  |  salary   |
+----+---------------+-------------+-----------+
| 1  | Brecca Bowley | Engineering | 85000.000 |
| 2  | Sash Smatt    | Marketing   | 75000.000 |
| 3  | Paylor Putrid | Engineering | 90000.000 |
+----+---------------+-------------+-----------+

It’s in table format as expected. Now let’s change that.

Changing to INSERT Mode

To switch to INSERT statement output mode, use the .mode command with the insert argument:

.mode insert

You can also specify a custom table name for the generated INSERT statements by adding it after insert:

.mode insert staff

In this example, any INSERT statements generated will insert the data into a table called staff. Change staff to the name of your destination table.

Verifying the Change

First of all, we can run .mode to check the mode:

.mode

Output:

current output mode: insert

Note that I had specified a table name when I specified insert mode but it doesn’t show the table name here.

But the proof is in the pudding. So let’s run a query to see how the output is formatted:

SELECT * FROM employees;

Output:

INSERT INTO staff(id,"name",department,salary) VALUES(1,'Brecca Bowley','Engineering',85000.000);
INSERT INTO staff(id,"name",department,salary) VALUES(2,'Sash Smatt','Marketing',75000.000);
INSERT INTO staff(id,"name",department,salary) VALUES(3,'Paylor Putrid','Engineering',90000.000);

As expected, the output is now formatted as INSERT statements. Each INSERT statement inserts into the staff table, which is the name of the table I provided when switching modes.

These generated INSERT statements can be:

• Copied and executed directly in another DuckDB database
• Modified for use with different table names
• Saved to a file for later use
• Used as a template for generating similar data

Omitting the Destination Table

We can omit the table name when switching modes. Here’s what happens when we do that:

.mode insert
SELECT * FROM employees;

Output:

INSERT INTO "table"(id,"name",department,salary) VALUES(1,'Brecca Bowley','Engineering',85000.000);
INSERT INTO "table"(id,"name",department,salary) VALUES(2,'Sash Smatt','Marketing',75000.000);
INSERT INTO "table"(id,"name",department,salary) VALUES(3,'Paylor Putrid','Engineering',90000.000);

This time all rows will be inserted into "table". We can replace this with another table name later if required.

Switching Back to the Original Mode

To switch back to the previous mode at any time, simply run:

.mode duckbox

Replacing duckbox with another mode if you prefer another mode.

Valid Modes

Here’s how to get a list of available modes:

.help mode

Output:

.mode MODE ?TABLE?       Set output mode
   MODE is one of:
     ascii     Columns/rows delimited by 0x1F and 0x1E
     box       Tables using unicode box-drawing characters
     csv       Comma-separated values
     column    Output in columns.  (See .width)
     duckbox   Tables with extensive features
     html      HTML <table> code
     insert    SQL insert statements for TABLE
     json      Results in a JSON array
     jsonlines Results in a NDJSON
     latex     LaTeX tabular environment code
     line      One value per line
     list      Values delimited by "|"
     markdown  Markdown table format
     quote     Escape answers as for SQL
     table     ASCII-art table
     tabs      Tab-separated values
     tcl       TCL list elements
     trash     No output