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.
CREATE TABLE new_table AS 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 ...