How to insert multiple rows in SQLite?

sql

bulk-operations

performance-optimization

sql-queries

byNikita Barsukov·Nov 1, 2024

Here's the concise command to insert multiple rows in SQLite:

INSERT INTO table_name (column1, column2) VALUES
('row1_value1', 'row1_value2'),
('row2_value1', 'row2_value2');

Rename table_name, column1, column2, and the row_values with your specific table and data.

Doing mass inserts? Wrap the insert commands within a transaction for turbo-charged performance!

BEGIN TRANSACTION;
/* Start the party */
INSERT INTO table_name (column1, column2) VALUES ('row1_value1', 'row1_value2');
/* Another one bites the dust */
INSERT INTO table_name (column1, column2) VALUES ('row2_value1', 'row2_value2');
...
COMMIT; /* Stop. Hammer time! */

Performing batch inserts this way is like giving SQLite a nitrous boost!

Handle with care: Legacy SQLite versions

Rocking an older SQLite, pre-3.7.11? Fear not! Use the UNION ALL SELECT command for multiple inserts:

INSERT INTO table_name (column1, column2)
SELECT 'value1a', 'value2a'
UNION ALL SELECT 'value1b', 'value2b'
UNION ALL SELECT 'value1c', 'value2c';

Remember, kids, in a UNION sequence, aliases are for the cool cats in the first SELECT only!

Optimize performance: Fast AND Furious

Transactions: The need-for-speed trick

Wrap your inserts in a single transaction to reduce disk I/O and watch SQLite do its Fast & Furious impression.

BEGIN TRANSACTION;
/* Unleash the beast */
INSERT INTO ...;
COMMIT; /* Cool down the engines */

Finish the transaction with a COMMIT—No racer leaves before the race is finished!

Write-Ahead Logging (WAL): Double lane highway

Ever felt the need for concurrent reads and writes? SQLite's Write-Ahead Logging (WAL) mode is your autobahn. Check out SQLite's WAL documentation before hitting the pedal.

Bulk operations: The power of the horde

Multi-row inserts: The power of the horde!

Consider this when handling bulk data operations: A horde is stronger than a single orc.

# Auto-generate inserts: Unleash the horde!
rows.map do |row|
  "INSERT INTO table_name VALUES #{row.join(",")};"
end.join

Error handling: The power of a good shepherd

Just remember: when handling bulk inserts, you need to be a good shepherd — handling exceptions, logging errors, and ensuring data consistency.

Common pitfalls: Be the cool SQL dude

Data type mismatches: Mixing apples with oranges

SQL is cool, but not when you mix data types! Stay vigilant, or you might spill your cocktail.

Unique constraints and duplicates: The unique snowflakes dilemma

Adding INSERT OR IGNORE to your queries keeps things chill if a snowflake decides to be a duplicate.

Advanced techniques: For the pros

PRAGMAs for insert optimization: The fine-tuning game

Ever tried the magic of PRAGMAs? PRAGMA cache_size; lets you DJ your session's cache size.

EXPLAIN QUERY PLAN: Into the matrix

Before hitting the button, look into the matrix with EXPLAIN QUERY PLAN. This can help you unravel SQLite’s mind before it performs the insert.

explain-codes / Sql / How to insert multiple rows in SQLite?

Linked

Python and SQLite: insert into table



How to dump the data of some SQLite3 tables?



How to get a list of column names on Sqlite3 database?



Sql INSERT INTO from multiple tables



How to do IF NOT EXISTS in SQLite



Is there a way to SELECT and UPDATE rows at the same time?



Sqlite: Select Into?



Handle with care: Legacy SQLite versions Optimize performance: Fast AND Furious Bulk operations: The power of the horde Common pitfalls: Be the cool SQL dude Advanced techniques: For the pros