How to Efficiently Insert Multiple Rows into an SQLite Database?

SQLite multi-row insertion skills

Unlike MySQL, SQLite itself does not directly support the concise syntax of inserting multiple rows at the same time. However, there is an alternative method to achieve a similar effect.

SQLite multi-row insert syntax

To insert multiple rows in SQLite, you can use the following syntax:

<code><br></br>INSERT INTO 'tablename'<br></br>SELECT 'data1' AS 'column1', 'data2' AS 'column2'<br></br>UNION ALL SELECT 'data1', 'data2'<br></br>UNION ALL SELECT 'data1', 'data2'<br></br>UNION ALL SELECT 'data1', 'data2'<br></br></code>

In this example, the data 'data1' and 'data2' will be inserted into the 'tablename' table four times.

The difference between UNION and UNION ALL

Using UNION ALL ensures that all rows are inserted, even if they contain duplicate data. If ALL is omitted, duplicate rows are eliminated.

Performance Considerations

While this approach allows multiple rows to be inserted, it is not necessarily more efficient than using a single INSERT statement within a transaction. For better performance, consider using the following syntax:

<code><br></br>BEGIN TRANSACTION;<br></br>INSERT INTO 'tablename' VALUES ('data1', 'data2');<br></br>INSERT INTO 'tablename' VALUES ('data3', 'data4');<br></br>...<br></br>COMMIT;<br></br></code>

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