How to Insert Multiple Rows in SQLite?

In SQLite, a single INSERT INTO query with several sets of values can be used to insert multiple rows. Alternatively, you can run different INSERT statements for every row independently. INSERT INTO… SELECT is an additional method for inserting several rows from a different table, which helps in improving the readability. In this blog, let us explore the different ways in which we can insert multiple rows in SQLite in detail with examples for each.

Table of Contents:

• Methods to Insert Multiple Rows in SQLite
  • Method 1: Using Single INSERT INTO … VALUES Statement
  • Method 2: Using Multiple INSERT INTO … VALUES Statement
  • Method 3: Using INSERT INTO … SELECT Statement
  • Method 4: Using SQLite transactions for Batch Insertion
• Alternative Approaches to Insert Multiple Rows
  • Method 1: Using Executemany() in Python with SQLite
  • Method 2: Using INSERT or REPLACE INTO Statements
• Performance Comparison of Each Method
• Best Practices
• Real-world Examples
• Conclusion
• How to Insert Multiple Rows in SQLite – FAQs

Methods to Insert Multiple Rows in SQLite

Using a single INSERT INTO… VALUES statement, running several INSERT statements, and using INSERT INTO… SELECT for bulk insertion are the three primary methods.

Before getting started with the methods of inserting multiple rows, let us create a Products table, which can be used as an example for the following methods

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    category TEXT NOT NULL,
    price REAL
);

Method 1: Using Single INSERT INTO … VALUES Statement

Using a single INSERT INTO Statement, multiple rows can be inserted into the table.

Syntax:

INSERT INTO table_name (column1, column2, column3, ...)  
VALUES  
    (value1, value2, value3, ...),  
    (value4, value5, value6, ...),  
    (value7, value8, value9, ...);

Example:

-- To insert multiple rows 
INSERT INTO products (name, category, price)  
VALUES  
    ('Laptop', 'Electronics', 800),  
    ('Table', 'Furniture', 150),  
    ('Headphones', 'Electronics', 50);  
-- To display the table 
Select * from products;

Output:

Explanation: A row is represented by each of the several sets of values in the VALUES Clause. As a result, every value is added to the product database.

Method 2: Using Multiple INSERT INTO … VALUES Statement

This method adds many rows to the table by executing separate INSERT INTO instructions for each entry.

Syntax:

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);  
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value4, value5, value6, ...);  
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value7, value8, value9, ...);

Example:

-- Inserting multiple rows using separate insert statement 
INSERT INTO products (name, category, price) VALUES ('Smartwatch', 'Electronics', 200);  
INSERT INTO products (name, category, price) VALUES ('Sofa', 'Furniture', 550);  
INSERT INTO products (name, category, price) VALUES ('Coffee Maker', 'Appliances', 120);  
-- To display the table 
Select * from products;

Output:

Explanation: Each INSERT INTO Statement inserts a new row into the table. Since the ID column is PRIMARY KEY, it is auto-incremented.

Method 3: Using INSERT INTO … SELECT Statement

A new row is added to the table with each INSERT INTO Statement. The ID column is auto-incremented because it is the primary key.

Syntax:

INSERT INTO target_table (column1, column2, column3, ...)  
SELECT value1, value2, value3, ...  
UNION ALL  
SELECT value4, value5, value6, ...  
UNION ALL  
SELECT value7, value8, value9, ...;

Example:

INSERT INTO products (name, category, price)  
SELECT 'Smartphone', 'Electronics', 600 UNION ALL  
SELECT 'Chair', 'Furniture', 120 UNION ALL  
SELECT 'Blender', 'Appliances', 80 UNION ALL  
SELECT 'Washing Machine', 'Appliances', 400;
-- To display the table 
Select * from products;

Output:

Explanation: A new row is added to the Products table containing a collection of data provided by the SELECT Statement. The UNION ALL ensures that no duplicates are eliminated when inserting each row.

Method 4: Using SQLite transactions for Batch Insertion

Performance can be increased by grouping several INSERT statements into a single unit using SQL Transactions.

Syntax:

BEGIN TRANSACTION;  
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);  
INSERT INTO table_name (column1, column2, ...) VALUES (value3, value4, ...);  
...  
COMMIT;

Example:

BEGIN TRANSACTION;
INSERT INTO products (name, category, price) VALUES ('Smartwatch', 'Electronics', 200);
INSERT INTO products (name, category, price) VALUES ('Sofa', 'Furniture', 550);
INSERT INTO products (name, category, price) VALUES ('Coffee Maker', 'Appliances', 120);
COMMIT;

-- If an error occurs
ROLLBACK;

Output:

Explanation: BEGIN TRANSACTIONS groups all the INSERT Statements under a single transaction. INSERT INTO Statements are executed as usual.

Alternative Approaches to Insert Multiple Rows

Other methods include the COPY for PostgreSQL, the executemany() function in SQLite3, and bulk_insert_mappings() in SQLAlchemy.

Method 1: Using Executemany() in Python with SQLite

Using the executemany() method in Python’s SQLite3 package, we can run a single SQL statement repeatedly with various data sets

Syntax:

cursor.executemany(SQL_QUERY, SEQUENCE_OF_VALUES)

Example:

import sqlite3

# Step 1: Connect to SQLite database

conn = sqlite3.connect(‘:memory:’) 

cursor = conn.cursor()

# Step 2: Create a table

cursor.execute(“””

    CREATE TABLE products (

        id INTEGER PRIMARY KEY,

        name TEXT NOT NULL,

        category TEXT NOT NULL,

        price REAL

    )

“””)

# Step 3: Data to insert (multiple rows)

data = [

    (‘Laptop’, ‘Electronics’, 800),

    (‘Table’, ‘Furniture’, 150),

    (‘Headphones’, ‘Electronics’, 50)

]

# Step 4: Use executemany() to insert multiple rows efficiently

cursor.executemany(“INSERT INTO products (name, category, price) VALUES (?, ?, ?)”, data)

# Step 5: Retrieve and print the inserted records

cursor.execute(“SELECT * FROM products”)

rows = cursor.fetchall()

for row in rows:

    print(row)

# Step 6: Commit changes and close the connection

conn.commit() conn.close()

Output:

Explanation: An in-memory database is created for temporary use using sqlite3.connect(‘:memory:’). As an alternative, these data can be kept in a file using sqlite3.connect(‘database.db’). cursor.fetchall() collects and displays the inserted row.

Note: This method cannot be run in an Online SQL Compiler. Use Google Colab or VS Code for the output.

Method 2: Using INSERT or REPLACE INTO Statements

You can use this technique to add new rows or replace existing rows if the database already contains the main key.

Syntax:

INSERT OR REPLACE INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

Example:

INSERT INTO products (name, category, price) VALUES ('Laptop', 'Electronics', 800);
INSERT INTO products (name, category, price) VALUES ('Table', 'Furniture', 150);
INSERT INTO products (name, category, price) VALUES ('Headphones', 'Electronics', 50);

INSERT OR REPLACE INTO products (id, name, category, price)  
VALUES (1, 'Laptop', 'Electronics', 850);  -- Updates Laptop price  
INSERT OR REPLACE INTO products (id, name, category, price)  
VALUES (4, 'Smartphone', 'Electronics', 500);  -- Inserts Smartphone
SELECT * FROM products;

Output:

Explanation: The INSERT OR REPLACE statement is used to match the current record with id = 1 (primary key), changing the laptop price from 800 to 850.

Performance Comparison of Each Method

Best Practices

• Transactions Should Be Used for Bulk Inserts in Any Situation: Perform the INSERT of many data using SQLite transactions (it begins with a BEGIN TRANSACTION and ends with the COMMIT) when inserting large datasets.
• When Copying Data from Other Sources, Use INSERT INTO… SELECT: To insert multiple rows from another table or query, USE THE INSERT INTO… SELECT method.
• Avoid Individual Inserts in Loops: Avoid inserting rows with multiple INSERT statements one at a time in the loop; use for batch inserts or other transaction support.
• On Conflicts, INSERT OR REPLACE Registers: When you want to INSERT rows, and when work would be done on duplicates based on a primary key, one way is to INSERT OR REPLACE INTO.

Real-world Examples

1. Inventory Management System: After receiving a shipment, a company must enter a batch of new products into the database of an inventory management system. This entails inputting many rows of data relating to each product, including the product name, category, and price.

Example:

-- Create a Product table
CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    category TEXT NOT NULL,
    price REAL NOT NULL
);
--  Inserting multiple rows using a transaction
BEGIN TRANSACTION;

INSERT INTO products (name, category, price) VALUES ('Laptop', 'Electronics', 800);
INSERT INTO products (name, category, price) VALUES ('Sofa', 'Furniture', 450);
INSERT INTO products (name, category, price) VALUES ('Smartphone', 'Electronics', 500);
INSERT INTO products (name, category, price) VALUES ('Table', 'Furniture', 120);
INSERT INTO products (name, category, price) VALUES ('Blender', 'Appliances', 80);

COMMIT;
-- To display the table
SELECT * FROM products;

Output:

Explanation: The INSERT INTO Statements are grouped within a BEGIN TRANSACTION and COMMIT block.

2. HR System: The business must enter daily attendance records for every employee in an employee attendance system. The employee ID, date, and attendance status (present or absent) for each day must be inserted into numerous rows.

Example:

-- Create a table
CREATE TABLE attendance (
    attendance_id INTEGER PRIMARY KEY AUTOINCREMENT,
    employee_id INTEGER NOT NULL,
    date TEXT NOT NULL,
    status TEXT NOT NULL,
    FOREIGN KEY (employee_id) REFERENCES employees (id)
);
-- Insert multiple attendance records
BEGIN TRANSACTION;

INSERT INTO attendance (employee_id, date, status) VALUES (1, '2025-03-06', 'Present');
INSERT INTO attendance (employee_id, date, status) VALUES (2, '2025-03-06', 'Absent');
INSERT INTO attendance (employee_id, date, status) VALUES (3, '2025-03-06', 'Present');
INSERT INTO attendance (employee_id, date, status) VALUES (4, '2025-03-06', 'Present');
INSERT INTO attendance (employee_id, date, status) VALUES (5, '2025-03-06', 'Absent');

COMMIT;
-- To display the table
SELECT * FROM attendance;

Output:

Explanation: The INSERT INTO Statements are grouped within a BEGIN TRANSACTION and COMMIT block. After committing the transactions, 5 new rows are inserted into the attendance table.

Conclusion

Here in this blog, we learn about how to insert multiple rows into SQLite. We have gone through many ways, such as using INSERT INTO … VALUES, INSERT INTO … SELECT, and SQLite transactions for greater insertions. Each one is better than the others, depending on performance and complexity. Transactions, for instance, will allow fast insertion of rows, especially in the case of large datasets. The INSERT INTO … SELECT is most effective for bulk transfers of data. The preferred method depends on how much data is there and the way of executing it.

To learn more about SQL functions, check out this SQL course and also explore SQL Interview Questions prepared by industry experts.

How to Insert Multiple Rows in SQLite – FAQs

INSERT INTO table_name (column1, column2) 
SELECT column1, column2 FROM another_table;