The SQLite command line interface provides us with the ability to auto-generate SQL INSERT statements from a query. This can be done by changing the output mode to insert.

Example

Suppose we have the following table:

SELECT * FROM Pets;

Result:

+-------+---------+--------+
| PetId | PetName | TypeId |
+-------+---------+--------+
| 1     | Homer   | 3      |
| 2     | Yelp    | 1      |
| 3     | Fluff   | 2      |
| 4     | Brush   | 4      |
+-------+---------+--------+

When I ran that example, I was using table mode. Table mode presents the query output as a table, and is one of several tabular output modes in SQLite.

We can change it to insert mode like this:

.mode insert Pets2

When you change to insert mode, you must specify the name of the table that the data will be inserted into. In my case, I specified Pets2, so the data will be inserted into that table.

Now when we run the same SQL query as before, we get a bunch of INSERT statements:

SELECT * FROM Pets;

Result:

INSERT INTO Pets2 VALUES(1,'Homer',3);
INSERT INTO Pets2 VALUES(2,'Yelp',1);
INSERT INTO Pets2 VALUES(3,'Fluff',2);
INSERT INTO Pets2 VALUES(4,'Brush',4);

Of course, when you go to insert these values into a table, you’ll need to make sure the table exists first. You can either create a table with the correct name, or insert into an existing one.