Is it possible to insert multiple rows at a time in an SQLite database

Question

In MySQL you can insert multiple rows like this   INSERT INTO  tablename    column1    column2   VALUES       data1    data2          data1    data2          data1    data2          data1    data2      However  I am getting an error when I try to do something like this  Is it possible to insert multiple rows at a time in an SQLite database  What is the syntax to do that

User · Answer

As of version 3 7 11 SQLite does support multi-row-insert  Richard   Hipp comments    I m using 3 6 13  I command like this   insert into xtable f1 f2 f3  select v1 as f1  v2 as f2  v3 as f3  union select nextV1   nextV2   nextV3    With 50 records inserted at a time  it takes only a second or less   It s true using sqlite to insert multiple rows at a time is very possible   By  Andy wrote   thanks Andy  1

User · Answer

You can t but I don t think you miss anything    Because you call sqlite always in process  it almost doesn t matter in performance whether you execute 1 insert statement or 100 insert statements  The commit however takes a lot of time so put those 100 inserts inside a transaction    Sqlite is much faster when you use parameterized queries  far less parsing needed  so I wouldn t concatenate big statements like this   insert into mytable  col1  col2  select  a   b  union  select  c   d  union       They need to be parsed again and again because every concatenated statement is different

User · Answer

INSERT INTO TABLE NAME               DATA1                DATA2   VALUES       VAL1                VAL2                 VAL1                VAL2                 VAL1                VAL2                 VAL1                VAL2                 VAL1                VAL2                 VAL1                VAL2                 VAL1                VAL2                 VAL1                VAL2

User · Answer

Sqlite3 can t do that directly in SQL except via a SELECT  and while SELECT can return a  row  of expressions  I know of no way to make it return a phony column   However  the CLI can do it    import FILE TABLE     Import data from FILE into TABLE  separator STRING      Change separator used by output mode and  import    sqlite3  tmp test db SQLite version 3 5 9 Enter   help  for instructions sqlite gt  create table abc  a   sqlite gt   import  dev tty abc 1 2 3 99  D sqlite gt  select   from abc  1 2 3 99 sqlite gt     If you do put a loop around an INSERT  rather than using the CLI  import command  then be sure to follow the advice in the sqlite FAQ for INSERT speed      By default  each INSERT statement is   its own transaction  But if you   surround multiple INSERT statements   with BEGIN   COMMIT then all the   inserts are grouped into a single   transaction  The time needed to commit   the transaction is amortized over all   the enclosed insert statements and so   the time per insert statement is   greatly reduced       Another option is to run PRAGMA   synchronous OFF  This command will   cause SQLite to not wait on data to   reach the disk surface  which will   make write operations appear to be   much faster  But if you lose power in   the middle of a transaction  your   database file might go corrupt

User · Answer

update As BrianCampbell points out here  SQLite 3 7 11 and above now supports the simpler syntax of the original post   However  the approach shown is still appropriate if you want maximum compatibility across legacy databases  original answer If I had privileges  I would bump river s reply  You can insert multiple rows in SQLite  you just need different syntax   To make it perfectly clear  the OPs MySQL example  INSERT INTO  tablename    column1    column2   VALUES     data1    data2        data1    data2        data1    data2        data1    data2     This can be recast into SQLite as       INSERT INTO  tablename            SELECT  data1  AS  column1    data2  AS  column2  UNION ALL SELECT  data1    data2  UNION ALL SELECT  data1    data2  UNION ALL SELECT  data1    data2   a note on performance I originally used this technique to efficiently load large datasets from Ruby on Rails   However  as Jaime Cook points out  it s not clear this is any faster wrapping individual INSERTs within a single transaction  BEGIN TRANSACTION  INSERT INTO  tablename  table VALUES   data1    data2    INSERT INTO  tablename  table VALUES   data3    data4        COMMIT   If efficiency is your goal  you should try this first  a note on UNION vs UNION ALL As several people commented  if you use UNION ALL  as shown above   all rows will be inserted  so in this case  you d get four rows of data1  data2   If you omit the ALL  then duplicate rows will be eliminated  and the operation will presumably be a bit slower    We re using UNION ALL since it more closely matches the semantics of the original post  in closing P S   Please  1 river s reply  as it presented the solution first

User · Answer

I have a query like below  but with ODBC driver SQLite has an error with     it says  I run vbscript in HTA  Html Application    INSERT INTO evrak ilac iliskileri  evrak id  ilac id  baglayan kullanici id  tarih  VALUES  4150 762 1 datetime     4150 9770 1 datetime     4150 6609 1 datetime     4150 3628 1 datetime     4150 9422 1 datetime

User · Answer

Start from version 2012-03-20  3 7 11   sqlite support the following INSERT syntax   INSERT INTO  tablename    column1    column2   VALUES     data1    data2        data3    data4        data5    data6        data7    data8      Read documentation  http   www sqlite org lang insert html  PS  Please  1 to Brian Campbell s reply answer  not mine  He presented the solution first

User · Answer

Yes it is possible  but not with the usual comma-separated insert values   Try this     insert into myTable  col1 col2        select aValue as col1 anotherValue as col2       union select moreValue evenMoreValue       union      Yes  it s a little ugly but easy enough to automate the generation of the statement from a set of values  Also  it appears you only need to declare the column names in the first select

User · Answer

As the other posters have said  SQLite does not support this syntax   I don t know if compound INSERTs are part of the SQL standard  but in my experience they re not implemented in many products   As an aside  you should be aware that INSERT performance in SQLite is improved considerably if you wrap multiple INSERTs in an explicit transaction

User · Answer

INSERT INTO tabela coluna1 coluna2   SELECT  texto   outro  UNION ALL  SELECT  mais texto   novo texto

User · Answer

I am able to make the query dynamic  This is my table    CREATE TABLE  tblPlanner    probid  text  userid  TEXT  selectedtime  DATETIME  plannerid  TEXT  isLocal  BOOL  applicationid  TEXT   comment  TEXT   subject  TEXT   and I m getting all data through a JSON  so after getting everything inside an NSArray I followed this       NSMutableString  query     NSMutableString alloc init       for  int i   0  i  lt  arr count  i                  NSString  sqlQuery   nil          sqlQuery    NSString stringWithFormat                                                                              arr objectAtIndex i  objectForKey   plannerid                          arr objectAtIndex i  objectForKey   probid                          arr objectAtIndex i  objectForKey   userid                          arr objectAtIndex i  objectForKey   selectedtime                          arr objectAtIndex i  objectForKey   isLocal                          arr objectAtIndex i  objectForKey   subject                          arr objectAtIndex i  objectForKey   comment                          NSUserDefaults standardUserDefaults  objectForKey   applicationid                                   query appendString sqlQuery                REMOVING LAST COMMA NOW      query deleteCharactersInRange NSMakeRange  query length -1  1         query    NSString stringWithFormat   insert into tblPlanner  plannerid  probid  userid  selectedtime  isLocal  applicationid  subject  comment  values    query     And finally the output query is this   insert into tblPlanner  plannerid  probid  userid  selectedtime  isLocal  applicationid  subject  comment  values   lt append 1 gt    pl1176428260        US32552    2013-06-08 12 00 44  0000    0    subj    Hiss    ap19788     lt append 2 gt    pl2050411638        US32552    2013-05-20 10 45 55  0000    0    TERI    Yahoooooooooo    ap19788      lt append 3 gt    pl1828600651        US32552    2013-05-21 11 33 33  0000    0    test    Yest    ap19788     lt append 4 gt    pl549085534        US32552    2013-05-19 11 45 04  0000    0    subj    Comment    ap19788      lt append 5 gt    pl665538927        US32552    2013-05-29 11 45 41  0000    0    subj    1234567890    ap19788      lt append 6 gt    pl1969438050        US32552    2013-06-01 12 00 18  0000    0    subj    Cmt    ap19788     lt append 7 gt    pl672204050        US55240280    2013-05-23 12 15 58  0000    0    aassdd    Cmt    ap19788      lt append 8 gt    pl1019026150        US32552    2013-06-08 12 15 54  0000    0    exists    Cmt    ap19788      lt append 9 gt    pl790670523        US55240280    2013-05-26 12 30 21  0000    0    qwerty    Cmt    ap19788     which is running well through code also and I m able to save everything in SQLite successfully   Before this i made UNION query stuff dynamic but that started giving some syntax error  Anyways  this is running well for me

User · Answer

I m surprised that no one has mentioned prepared statements  Unless you are using SQL on its own and not within any other language  then I would think that prepared statements wrapped in a transaction would be the most efficient way of inserting multiple rows

User · Answer

in mysql lite you cannot insert multiple values  but you can save time by opening connection only one time and then doing all insertions and then closing connection  It saves a lot of time

User · Answer

The problem with using transaction is that you lock the table also for reading  So if you have really much data to insert and you need to access to your data  for exemple a preview or so  this way doesn t work well   The problem with the other solution is that you lose the order of the inserting  insert into mytable  col  select  c  union  select  d  union  select  a  union  select  b     In the sqlite the data will be store a b c d

User · Answer

According to this page it is not supported         2007-12-03   Multi-row INSERT a k a  compound INSERT not supported          INSERT INTO table  col1  col2  VALUES          row1col1    row1col2      row2col1    row2col2             Actually  according to the SQL92 standard  a VALUES expression should be able to stand on itself  For example  the following should return a one-column table with three rows  VALUES  john    mary    paul     As of version 3 7 11 SQLite does support multi-row-insert  Richard Hipp comments       The new multi-valued insert is merely syntactic suger  sic  for the compound   insert   There is no performance advantage one way or the other

User · Answer

On sqlite 3 7 2   INSERT INTO table name  column1  column2                   SELECT  value1    value1             UNION SELECT  value2    value2             UNION SELECT  value3    value3     and so on

User · Answer

If you are using bash shell you can use this    time bash -c    FILE  dev shm test db sqlite3  FILE  create table if not exists tab id int    sqlite3  FILE  insert into tab values  1   2   for i in 1 2 3 4  do sqlite3  FILE  INSERT INTO tab  id  select  a id b id c id  abs random   1e7  from tab a  tab b  tab c limit 5e5   done   sqlite3  FILE  select count    from tab      Or if you are in sqlite CLI  then you need to do this    create table if not exists tab id int    insert into tab values  1   2   INSERT INTO tab  id  select  a id b id c id  abs random   1e7  from tab a  tab b  tab c limit 5e5  INSERT INTO tab  id  select  a id b id c id  abs random   1e7  from tab a  tab b  tab c limit 5e5  INSERT INTO tab  id  select  a id b id c id  abs random   1e7  from tab a  tab b  tab c limit 5e5  INSERT INTO tab  id  select  a id b id c id  abs random   1e7  from tab a  tab b  tab c limit 5e5  select count    from tab    How does it work   It makes use of that if table tab    id int ------ 1 2   then select a id  b id from tab a  tab b returns  a id int   b id int ------------------     1      1     2      1     1      2     2      2   and so on  After first execution we insert 2 rows  then 2 3 8   three because we have tab a  tab b  tab c   After second execution we insert additional  2 8  3 1000 rows  Aftern thrid we insert about max 1000 3  5e5  500000 rows and so on     This is the fastest known for me method of populating SQLite database

User · Answer

Alex is correct  the  select     union  statement will lose the ordering which is very important for some users   Even when you insert in a specific order  sqlite changes things so prefer to use transactions if insert ordering is important   create table t example  qid int not null  primary key  qid    begin transaction  insert into  t example   qid  values  8   insert into  t example   qid  values  4   insert into  t example   qid  values  9   end transaction       select rowid   from t example  1 8 2 4 3 9

User · Answer

fearless fool has a great answer for older versions  I just wanted to add that you need to make sure you have all the columns listed  So if you have 3 columns  you need to make sure select acts on 3 columns   Example  I have 3 columns but I only want to insert 2 columns worth of data  Assume I don t care about the first column because it s a standard integer id  I could do the following     INSERT INTO  tablename        SELECT NULL AS  column1    data1  AS  column2    data2  AS  column3  UNION SELECT NULL   data3    data4  UNION SELECT NULL   data5    data6  UNION SELECT NULL   data7    data8    Note  Remember the  select     union  statement will lose the ordering   From AG1

User · Answer

Yes  as of SQLite 3 7 11 this is supported in SQLite  From the SQLite documentation      when this answer was originally written  this was not supported   For compatibility with older versions of SQLite  you can use the trick suggested by andy and fearless fool using UNION  but for 3 7 11 and later the simpler syntax described in here should be preferred

User · Answer

Yes  sql can do this  but with a different syntax  The sqlite documentation is pretty good  by the way  It will also tell you that the only way to insert several row is use a select statement as the source of the data to be inserted

User · Answer

I wrote some ruby code to generate a single 500 element multi-row insert from a series of insert statements which was considerably faster than running the individual inserts  Then I tried simply wrapping the multiple inserts into a single transaction and found that I could get the same kind of speed up with considerably less code    BEGIN TRANSACTION  INSERT INTO table VALUES  1 1 1 1   INSERT INTO table VALUES  2 2 2 2       COMMIT

User · Answer

If you use the Sqlite manager firefox plugin  it supports bulk inserts from INSERT SQL statements   Infact it doesn t support this  but Sqlite Browser does  works on Windows  OS X  Linux

User · Answer

you can use InsertHelper  it is easy and fast  documentation  http   developer android com reference android database DatabaseUtils InsertHelper html  tutorial  http   www outofwhatbox com blog 2010 12 android-using-databaseutils-inserthelper-for-faster-insertions-into-sqlite-database   Edit  InsertHelper is deprecated as of API Level 17

[sql] Is it possible to insert multiple rows at a time in an SQLite database?

Examples related to sql

Examples related to sqlite

Examples related to syntax