I want to create a table using query result. But I want to also add a auto increment primary key field to it. Is it possible to achieve it using SQLite?
Select two fields from table_a. But I want the output schema as (id, field_a, field_b).
create table foo as
select field_a, field_b
This is not possible with a single statement;
CREATE TABLE ... AS ... does not create constraints.
You have to use two statements:
CREATE TABLE foo ( ID INTEGER PRIMARY KEY, [...] ); INSERT INTO foo (...) SELECT ...;