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