title: Sqlite: Create new table for query results and keep the original schema
body: <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>