The SQLite command line interface has a handy dot command called .mode, which allows you to change the way query results are formatted.

One of the values you can use with this command is insert. Using this value results in all subsequent query results being formatted as an SQL INSERT statement.

SQLite Default Settings

First, here’s what your normal results might look like (assuming you’re using the SQLite default settings).

SELECT * FROM Products;

Result:

1|Widget Holder|139.5
2|Widget Opener|89.7
3|Widgets - 6 Pack|374.2
4|Blue Widget|63.0

Results as INSERT Statement

Now here’s how to change it so that the results are formatted as an SQL INSERT statement:

.mode insert Products2

That statement specifies Products2 as the table that the data will be inserted into. You would need to specify the name of the table that you want to insert the data into.

Now when I run the previous SELECT statement again, they’re output as an INSERT statement.

SELECT * FROM Products;

Result:

INSERT INTO Products2 VALUES(1,'Widget Holder',139.49999999999999999);
INSERT INTO Products2 VALUES(2,'Widget Opener',89.700000000000002838);
INSERT INTO Products2 VALUES(3,'Widgets - 6 Pack',374.19999999999998862);
INSERT INTO Products2 VALUES(4,'Blue Widget',63.000000000000000001);

Save this Format

When you open SQLite in a new terminal window, you’ll lose these settings. In other words, SQLite will revert back to its default output format (which is a pipe-separated list).

However, you can actually specify your own default settings by saving the above settings into a text file.

To do this, enter the following into a text file:

.mode insert NewTable

Then save that file as .sqliterc in your/(the user’s) home directory.

Now when you connect to SQLite in a new terminal window, it will use those settings instead of the default SQLite settings.

This example uses NewTable as the target table but you can use whatever you want.

Obviously, the only problem with doing this is that you will need to change the name of the table if it changes. And if you need to go into the trouble of doing that, it might be easier to simply change it on the fly with .mode insert NewTable (instead of needing to find/replace the table name). In any case, it’s an option if you need it.