How to insert multiple rows in SQLite?

Inserting multiple rows in SQLite can be achieved through several different methods, depending on the specific use case and data volume. Below, I will outline several commonly used methods:

1. Using a Single INSERT Statement to Insert Multiple Rows

SQLite supports inserting multiple rows of data in a single INSERT statement. This method is more efficient and is suitable when all the data to be inserted is known at the time of coding. The syntax is as follows:

sql
INSERT INTO table_name (column1, column2, column3, ...)
VALUES
    (value1A, value2A, value3A, ...),
    (value1B, value2B, value3B, ...),
    (value1C, value2C, value3C, ...);

For example, suppose we have a table named students with three fields: id, name, and age. We can insert data as follows:

sql
INSERT INTO students (id, name, age)
VALUES
    (1, 'Alice', 21),
    (2, 'Bob', 22),
    (3, 'Charlie', 23);

2. Using Multiple INSERT Statements

If the data is generated gradually or collected at different times, multiple INSERT statements can be used to insert each row separately. Although this approach is straightforward, it is less efficient when handling large volumes of data.

sql
INSERT INTO students (id, name, age) VALUES (1, 'Alice', 21);
INSERT INTO students (id, name, age) VALUES (2, 'Bob', 22);
INSERT INTO students (id, name, age) VALUES (3, 'Charlie', 23);

3. Using Transactions for Bulk Insertions

When inserting large amounts of data, using transactions can significantly improve performance. This is because transactions reduce disk I/O and waiting time. You can wrap multiple INSERT statements between BEGIN TRANSACTION and COMMIT.

sql
BEGIN TRANSACTION;
INSERT INTO students (id, name, age) VALUES (1, 'Alice', 21);
INSERT INTO students (id, name, age) VALUES (2, 'Bob', 22);
INSERT INTO students (id, name, age) VALUES (3, 'Charlie', 23);
COMMIT;

Using transactions not only improves performance but also helps ensure data integrity.

Summary

The choice of method primarily depends on the specific use case and the volume of data being operated on. For datasets that are known and relatively small, using a single INSERT statement to insert multiple rows is the simplest and most efficient method. For larger datasets or dynamically generated data, using transactions to handle INSERT statements can improve efficiency and ensure data consistency.