SQLite Forum: slow import of CSV files

Dear Richard,
Dear SQLiters,


Let me see if I understood you correctly. In order to re-create table with all constraints from a 30GB CSV file with 100M records using sqlite3 shell I have to follow 3 steps :

I schematically describe the SQL commands keeping important things. If I do not list the important, then I missed it, please correct.

1. .import 'large.csv' QQQ_1. In table QQQ_1 all types are TEXT.
2. CREATE table QQQ_2( columns with proper type, but not unique or primary constraints).
   PRAGMA schema.cache_size = -1000000; -- set cache to 1G
   INSERT INTO QQQ_2 SELECT FROM QQQ_1;
   CREATE UNIQUE INDEX ON QQQ_2 to emulate PRIMARY KEY/unique constraints;
3. CREATE table official( columns with proper type, AND proper unique/primary constraints).
   INSERT INTO official SELECT FROM QQQ_2 ORDER BY list of columns in pk;

Drop tables QQQ_1 and QQQ_2.

Thank you, as always, for your help,

Roman