Sqlite: Create new table for query results and keep the original schema
<p>I have the following db file (DB1.db) contains only one table:</p>

<pre><code>sqlite&gt; .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE CARS (ID INT KEY NOT NULL, COMPANY CHAR(20) NOT NULL, COLOR CHAR(10) NOT NULL);
INSERT INTO "CARS" VALUES(111,'Hyundai','White');
INSERT INTO "CARS" VALUES(222,'BMW','Black');
INSERT INTO "CARS" VALUES(333,'Toyota','Blue');
COMMIT;
</code></pre>

<p>In order to join between two tables in two different databases, I attach the DB1.db (and the other db) into DB2.db:</p>

<pre><code>sqlite&gt; ATTACH DATABASE 'DB1.db' AS 'db1' ;
</code></pre>

<p>The 'CARS' table info looks like:</p>

<pre><code>sqlite&gt; PRAGMA TABLE_INFO(CARS);
0|ID|INT KEY|1||0
1|COMPANY|CHAR(20)|1||0
2|COLOR|CHAR(10)|1||0
</code></pre>

<p>Next and final, I want to run query on the attached tables and save the result in new table in DB2.db:</p>

<pre><code>sqlite&gt; CREATE TABLE RES1 AS SELECT ID,COMPANY FROM CARS WHERE ID='333';
</code></pre>

<p>And as I expected, the result is in a new table:</p>

<pre><code>sqlite&gt; SELECT * FROM RES1 ;
333|Toyota
</code></pre>

<p>But the new table info looks like:</p>

<pre><code>sqlite&gt; PRAGMA TABLE_INFO(RES1);
0|ID|INT|0||0
1|COMPANY|TEXT|0||0
</code></pre>

<p>And as you can see, I lost the 'COMPANY' column type - it is no longer 'CHAR(20)' and the column might be empty ('NOT NULL' is 0 and not 1 as before).</p>

<p><strong>My question:</strong>
How can I do the operations above (attach and query result into new table) and still not lose the original table information?</p>