How to Insert Multiple Rows in SQLite in One Query

When working with SQLite databases, there might be scenarios where you need to insert multiple rows into a table with a single query. Doing so can offer performance benefits as it reduces the overhead of multiple insert statements within a database transaction. In this article, we'll explore how to insert multiple rows in SQLite in one query and provide examples using various programming languages to achieve this.

Understanding the Basics

Before diving into the code examples, let's discuss the SQL syntax used to insert multiple rows into a SQLite table. Here's a basic example:

INSERT INTO table_name (column1, column2) VALUES 
  (value1a, value2a), 
  (value1b, value2b), 
  (value1c, value2c);

This query inserts three rows into the table named table_name. Each row is enclosed in parentheses, and different rows are separated by commas.

Inserting Multiple Rows Using Python

Python, with its sqlite3 module, provides a simple interface for SQLite operations. Here's how you can use it to insert multiple rows:

import sqlite3

# Connect to the SQLite database (or create it if it doesn't exist)
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Create a table
cursor.execute('''CREATE TABLE IF NOT EXISTS employees (
                   id INTEGER PRIMARY KEY,
                   name TEXT,
                   age INTEGER)''')

# Data to insert
employees_data = [(1, 'Alice', 30), 
                  (2, 'Bob', 24), 
                  (3, 'Charlie', 29)]

# Insert multiple records
cursor.executemany('''INSERT INTO employees (id, name, age) 
                      VALUES (?, ?, ?)''', employees_data)

# Commit and close the connection
conn.commit()
conn.close()

In this example, we used the executemany method to efficiently insert multiple rows with a single query. SQLite's parameter substitution reduces SQL injection risks and helps with performance.

Using SQLite in SQL Scripts

If working directly with SQL scripts rather than through a programming language, you can directly execute a similar query as shown earlier using any SQLite database manager that supports raw SQL execution (like SQLite3 CLI or a DB manager app).

Inserting Multiple Rows Using JavaScript (Node.js)

Let's see how you could achieve similar functionality in JavaScript using Node.js and the sqlite3 package:

const sqlite3 = require('sqlite3').verbose();

// Create a new database object
let db = new sqlite3.Database('example.db');

// Create a new table if it does not exist
db.run(`CREATE TABLE IF NOT EXISTS employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  age INTEGER)`);

// Insert multiple rows using a transaction
const employeesData = [
  [1, 'Alice', 30],
  [2, 'Bob', 24],
  [3, 'Charlie', 29]
];

// Start a transaction
db.serialize(() => {
  const stmt = db.prepare('INSERT INTO employees (id, name, age) VALUES (?, ?, ?)');
  employeesData.forEach(employee => stmt.run(employee));
  stmt.finalize();
});

db.close();

Using Node.js, we employ transactions and prepared statements to insert multiple records, maintaining the integrity and speed of database operations. The serialize() method is used to ensure that the prepared statements execute sequentially, not simultaneously, thus keeping database operations safe and predictable.

Conclusion

Inserting multiple rows at once in SQLite is a common requirement that can significantly boost performance by reducing the execution time associated with multiple queries. Depending on the language you're working in, there are various approaches to achieve this while ensuring performance and security standards are maintained. Adopting parameterized queries aids not only in efficient operations but helps in safeguarding against SQL injection attacks. Hopefully, the above examples provide you with a clearer understanding and implementation that suits your needs. Happy coding!