jeudi 3 mars 2016

Create new table for query results and keep the original schema in sqlite

I have the following db file (DB1.db) contains only one table:

sqlite> .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;

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

sqlite> ATTACH DATABASE 'DB1.db' AS 'db1' ;

The 'CARS' table info looks like:

sqlite> PRAGMA TABLE_INFO(CARS);
0|ID|INT KEY|1||0
1|COMPANY|CHAR(20)|1||0
2|COLOR|CHAR(10)|1||0

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

sqlite> CREATE TABLE RES1 AS SELECT ID,COMPANY FROM CARS WHERE ID='333';

And as I expected, the result is in a new table:

sqlite> SELECT * FROM RES1 ;
333|Toyota

But the new table info looks like:

sqlite> PRAGMA TABLE_INFO(RES1);
0|ID|INT|0||0
1|COMPANY|TEXT|0||0

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).

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

Aucun commentaire:

Enregistrer un commentaire