SQLite Forum

3 incorrect tables created during import
Login
BTW. Below is a zero impact 4 line patch which introduces .import --temp option thereby elevating available behaviour to the standard established in published code exemplar csv.c virtual table's default behaviour.

--- shell.c.in-3370100	2021-12-30 07:56:33.000000000 -0800
+++ shell.c.in-3370100-import-temp-csv	2022-01-09 09:52:11.349923000 -0800
@@ -8336,7 +8336,8 @@
     int eVerbose = 0;           /* Larger for more console output */
     int nSkip = 0;              /* Initial lines to skip */
     int useOutputMode = 1;      /* Use output mode to determine separators */
-
+    int useTemp = 0;		 /* If table created, use TEMP schema */
+    
     failIfSafeMode(p, "cannot run .import in safe mode");
     memset(&sCtx, 0, sizeof(sCtx));
     sCtx.z = sqlite3_malloc64(120);
@@ -8377,6 +8378,8 @@
         sCtx.cRowSep = '\n';
         xRead = csv_read_one_field;
         useOutputMode = 0;
+      }else if( strcmp(z,"-temp")==0 ){
+        useTemp = 1;
       }else{
         utf8_printf(p->out, "ERROR: unknown option: \"%s\".  Usage:\n", z);
         showHelp(p->out, "import");
@@ -8479,7 +8482,9 @@
     rc = sqlite3_prepare_v2(p->db, zSql, -1, &pStmt, 0);
     import_append_char(&sCtx, 0);    /* To ensure sCtx.z is allocated */
     if( rc && sqlite3_strglob("no such table: *", sqlite3_errmsg(p->db))==0 ){
-      char *zCreate = sqlite3_mprintf("CREATE TABLE \"%w\"", zTable);
+      char *zCreate;
+      if (useTemp) zCreate = sqlite3_mprintf("CREATE TEMP TABLE \"%w\"", zTable);
+      else zCreate = sqlite3_mprintf("CREATE TABLE \"%w\"", zTable);
       char cSep = '(';
       while( xRead(&sCtx) ){
         zCreate = sqlite3_mprintf("%z%c\n  \"%w\" TEXT", zCreate, cSep, sCtx.z);
@@ -8500,8 +8505,10 @@
       rc = sqlite3_exec(p->db, zCreate, 0, 0, 0);
       sqlite3_free(zCreate);
       if( rc ){
-        utf8_printf(stderr, "CREATE TABLE \"%s\"(...) failed: %s\n", zTable,
+        if (useTemp) utf8_printf(stderr, "CREATE TEMP TABLE \"%s\"(...) failed: %s\n", zTable,
                 sqlite3_errmsg(p->db));
+        else utf8_printf(stderr, "CREATE TABLE \"%s\"(...) failed: %s\n", zTable,
+                sqlite3_errmsg(p->db));                
         import_cleanup(&sCtx);
         rc = 1;
         goto meta_command_exit;