Insert many items from list into SQLite-CodePudding

I have a list of lots of data (will be near 1000). I want to add it all in one go to a row. Is this straight forward like a for loop over list with multiple inserts?multiple commits? Is this bad practice?thanks

I haven’t tried yet as just setting up table columns which is many so need to know if feasible thanks

CodePudding user response：

If you're using SQL to insert:

INSERT INTO 'tablename' ('column1', 'column2') VALUES
  ('data1', 'data2'),
  ('data1', 'data2'),
  ('data1', 'data2'),
  ('data1', 'data2');

If you're using code... generate that above query using a for loop then run it.

For a more efficient approach consider a union as shown in: Is it possible to insert multiple rows at a time in an SQLite database?

insert into 'tablename' ('column1','column2') 
     select data1 as 'column1',data2 as 'column2'
     union select data3,data4
     union...

CodePudding user response：

In sqlite you don't have network latency, so it does not really matter performance wise to issue many small requests toward the engine. For more reference about that you can read this page from the official documentation: https://www.sqlite.org/np1queryprob.html

But in write mode (insert or update), each individual query will have to pay the cost of an implicit transaction. To avoid that you need to gather your insert queries in an explicit transaction. Depending of your programming language, how you do that may vary. Here is a code sample on how to do that in go. I've simplified error code management, to have a better view of the gist.

tx, _ := db.Begin() 
for _, item := range items {
    tx.Exec(`INSERT INTO testtable (col1, col2) VALUES (?, ?)`, item.Field1, item.Field2)
}
tx.Commit()

If you detect an error in your loop instead calling tx.Commit() you need to call tx.Rollback() in order to cancel all previous writes to your database so that the final state is as if no insert query has been issued at all.