How Do I Insert Multiple Rows into an SQLite Table?

SQLite's Approach to Multi-Row Insertion

Inserting multiple rows into an SQLite table differs from methods used in databases like MySQL. Instead of directly listing values, SQLite utilizes a query-based approach.

Here's the SQLite syntax for multi-row insertion:

<code class="language-sql">INSERT INTO tablename
SELECT 'data1' AS column1, 'data2' AS column2
UNION ALL SELECT 'data3', 'data4'
UNION ALL SELECT 'data5', 'data6'
UNION ALL SELECT 'data7', 'data8';</code>

Explanation:

• The initial line designates the target table.
• Each subsequent SELECT statement defines a single row's data. The AS keyword is optional for column aliases.
• UNION ALL concatenates the results of these SELECT statements, efficiently inserting all rows simultaneously.

This method effectively handles rows with identical values. For distinct rows, simply adjust the data within each SELECT statement accordingly.

While transactions offer an alternative (grouping individual INSERT statements), they generally don't provide substantial performance gains compared to the UNION ALL technique.

The above is the detailed content of How Do I Insert Multiple Rows into an SQLite Table?. For more information, please follow other related articles on the PHP Chinese website!