code_junkie

Faster bulk inserts in sqlite3?

Nov 12th, 2011
79
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.96 KB | None | 0 0
  1. [ramanujan:~]$cat demotab.txt
  2. 44 92
  3. 35 94
  4. 43 94
  5. 195 49
  6. 66 28
  7. 135 93
  8. 135 91
  9. 67 84
  10. 135 94
  11.  
  12. [ramanujan:~]$echo "create table mytable ( col1 int, col2 int);" | sqlite3 foo.sqlite
  13. [ramanujan:~]$echo ".import demotab.txt mytable" | sqlite3 foo.sqlite
  14.  
  15. [ramanujan:~]$sqlite3 foo.sqlite
  16. -- Loading resources from /Users/ramanujan/.sqliterc
  17. SQLite version 3.6.6.2
  18. Enter ".help" for instructions
  19. Enter SQL statements terminated with a ";"
  20. sqlite> select * from mytable;
  21. col1 col2
  22. 44 92
  23. 35 94
  24. 43 94
  25. 195 49
  26. 66 28
  27. 135 93
  28. 135 91
  29. 67 84
  30. 135 94
  31.  
  32. sqlite> .show .separator
  33. echo: off
  34. explain: off
  35. headers: on
  36. mode: list
  37. nullvalue: ""
  38. output: stdout
  39. separator: "t"
  40. width:
  41.  
  42. create virtual table vtYourDataset using yourModule;
  43. -- Bulk insert
  44. insert into yourTargetTable (x, y, z)
  45. select x, y, z from vtYourDataset;
  46.  
  47. BEGIN;
  48. INSERT INTO table VALUES ();
  49. INSERT INTO table VALUES ();
  50. ...
  51. END;
Add Comment
Please, Sign In to add comment