How to Insert Multiple Rows in SQLite?

In the area of database management, efficiency is key. When working with SQLite, a lightweight database engine, inserting multiple rows efficiently can significantly boost performance and speed our workflow.

In this article, We will understand how to insert multiple rows in SQLite through various methods along with the examples and so on.

How to Insert Multiple Rows in SQLite?

When dealing with large datasets, inserting multiple rows can be more efficient than inserting them one by one. SQLite offers several methods to achieve this. Below are the methods which are used to Insert Multiple Rows are as follow:

1. Using INSERT INTO Statement
   
2. Selecting from another Table

To understand How to insert multiple rows in SQLite we need a table on which we will perform various operations and queries. Here we will consider a table called RealTimeData which contains data_id, data_name, data_value, and data_timestamp as Columns.

CREATE TABLE RealTimeData (
    data_id INT PRIMARY KEY,
    data_name VARCHAR(100),
    data_value VARCHAR(255),
    data_timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);

1. Using INSERT INTO Statement

INSERT INTO helps us to add multiple rows in a table by specifying the column names and then we can give values to it. The syntax of the INSERT INTO is given below:

INSERT INTO TABLENAME (columnn1, [column2]...)
VALUES (row1 values),
(row2 values),

In the column list, we can give the name of our columns and then after values, we can give the values for each row.

Example:

INSERT INTO RealTimeData (data_id, data_name, data_value) 
VALUES (1, 'Temperature', '25.6'),
       (2, 'Humidity', '60%'),
       (3, 'Pressure', '1013.25'),
       (4, 'Wind Speed', '12.5');

SELECT * from RealTimeData

Output:

Output of INSERT INTO

2. Selecting From Another Table

Let’s say we have a table from which we have to insert the rows into our table. Then we can do that with Select and INSERT INTO. In this method, we will select the columns and the rows using SELECT query and then we will insert those values directly into our Table.

Syntax:

INSERT INTO TABLENAME
SELECT col1, [col2]
from AnotherTable

This is a very useful approach many times when we have to make a copy of any particular dataset or we have to make a backup of the table at that time we can use such methods. Only the limitation is that the columns of both tables should be the same or their type and the count should be the same.

Insert INTO RealTimeData 
SELECT * from RealTimeDataAnother

Output:

Data from Another table was added to it.

Conclusion

Overall, inserting multiple rows efficiently is important for optimizing database performance. By utilizing the INSERT INTO statement with multiple value sets, using transactions for bulk inserts. Now after reading whole article you have clear understanding of how to insert multiple rows very easily.