SQLite - Create table from query results

By xngo on December 6, 2019

In SQLite, you can create a table from a query results using the CREATE TABLE ... AS SELECT statement. That statement will create and populate a database table based on the results of the SELECT statement. Here is an example.

    SELECT * 
    FROM Contacts AS c
        LEFT JOIN Emails AS e ON
            c.id = e.Contact_id
    WHERE c.id < 10

Note: If you have duplicate column names due to multiple joins, SQLite will suffix table name with :1 for the 1st duplicate name, :2 for the 2nd duplicate name and so on.

Insert rows from query results

With the above statement, you were able to create a table from the results of a query. But, how about keep inserting more rows into the newly created table from a query. Here is how to do it with INSERT INTO tablename SELECT... statement.

INSERT INTO new_table(col_1, col_2)
    SELECT col_1, col_2
        FROM ...

About the author

Xuan Ngo is the founder of OpenWritings.net. He currently lives in Montreal, Canada. He loves to write about programming and open source subjects.