Re: [sqlite] Dumping in a more portable format

[Dennis Cote]

Steve Bergman wrote:

Is it possible to dump an SQLite table in a format something like this?
INSERT INTO employee (last_name, first_name, mi, username, id, 
extension, home_phone, cell_phone, status, email_primary, 
email_secondary) VALUES ('Jones', 'John', NULL, 'jjones', 6, '108/14', 
'555-1212', '525-2123', 'a', '[EMAIL PROTECTED]', NULL);

I have a need to move data between DBMS's where the field order may 
not be known.

Thanks,
Steve Bergman




Steve,

This patch to shell.c (which is untested) should give you what you want 
(or at least something close).

--- old_shell.c    Mon Apr  3 17:16:09 2006
+++ shell.c    Mon Apr  3 17:17:17 2006
@@ -687,6 +687,19 @@
    if( zTmp ){
      zSelect = appendText(zSelect, zTmp, '\'');
    }
+    zSelect = appendText(zSelect, " || '(", 0);
+    rc = sqlite3_step(pTableInfo);
+    while( rc==SQLITE_ROW ){
+      const char *zText = (const char *)sqlite3_column_text(pTableInfo, 1);
+      zSelect = appendText(zSelect, zText, '"');
+      rc = sqlite3_step(pTableInfo);
+      if( rc==SQLITE_ROW ){
+        zSelect = appendText(zSelect, ", ", 0);
+      }else{
+        zSelect = appendText(zSelect, ")'", 0);
+      }
+    }
+    sqlite3_reset(pTableInfo);
    zSelect = appendText(zSelect, " || ' VALUES(' || ", 0);
    rc = sqlite3_step(pTableInfo);
    while( rc==SQLITE_ROW ){


Dennis Cote