SQLite CREATE TABLE AS SELECT

SQLite CREATE TABLE AS SELECT is a SQL statement in the SQLite Specific category. SQLite: CTAS creates a new table with the same structure and data from a query result. The syntax is CREATE TABLE new_table AS SELECT * FROM source;. It returns table from query. A typical example: CREATE TABLE employees_backup AS SELECT * FROM employees; -- Creates backup with all data CREATE TABLE high_earners AS SELECT * FROM employees WHERE salary > 100000; -- Filtered copy CREATE TABLE stats AS SELECT department, COUNT(*) AS cnt, AVG(salary) AS avg_sal FROM employees GROUP BY department; -- Aggregated summary table -- Note: SQLite CTAS does NOT copy: -- - PRIMARY KEY -- - FOREIGN KEY constraints -- - UNIQUE constraints… A close relative is AUTOINCREMENT (SQLite), which sQLite: unlike AUTO_INCREMENT in MySQL, only INTEGER PRIMARY KEY columns auto-increment. AUTOINCREMENT guarantees unique increasing IDs. A close relative is ROWID / _ROWID_, which sQLite: every row has an implicit 64-bit ROWID. INTEGER PRIMARY KEY becomes an alias for ROWID.