dark

How to Insert Multiple Rows in a Single SQL Query

How to Insert Multiple Rows in a Single SQL Query


One of the most common operations in SQL is inserting data into a table. In some cases, you may need to insert multiple rows into a table at once. This tutorial will show you how to insert multiple rows in a single SQL query.

Step 1: Create a Table

Before we can insert data into a table, we need to create a table. For this tutorial, we will create a simple table called “employees” with the following columns:

  • id (integer)
  • name (text)
  • email (text)
  • salary (integer)

Here’s the SQL code to create the table:

CREATE TABLE employees (
     id INTEGER PRIMARY KEY,
     name TEXT NOT NULL,
     email TEXT NOT NULL,
     salary INTEGER
 );

Step 2: Insert a Single Row

Let’s start by inserting a single row into the “employees” table. Here’s the SQL code to do that:

INSERT INTO employees (id, name, email, salary)
 VALUES (1, 'John Doe', 'john.doe@example.com', 50000);

This code will insert a single row with the values “1”, “John Doe”, “john.doe@example.com”, and “50000” into the “employees” table.

Step 3: Insert Multiple Rows

Now let’s move on to inserting multiple rows. To insert multiple rows in a single SQL query, we need to use the “INSERT INTO” statement followed by the name of the table and the columns we want to insert data into. After that, we use the “VALUES” keyword followed by a comma-separated list of values for each row we want to insert.

Here’s an example SQL code to insert two rows into the “employees” table:

INSERT INTO employees (id, name, email, salary)
 VALUES (2, 'Jane Doe', 'jane.doe@example.com', 60000),
        (3, 'Bob Smith', 'bob.smith@example.com', 70000);

This code will insert two rows into the “employees” table. The first row will have the values “2”, “Jane Doe”, “jane.doe@example.com”, and “60000”. The second row will have the values “3”, “Bob Smith”, “bob.smith@example.com”, and “70000”.

Step 4: Insert Multiple Rows Using a Subquery

Another way to insert multiple rows into a table is to use a subquery. A subquery is a query that is nested inside another query. In this case, we can use a subquery to select the data we want to insert and then insert it into the table.

Here’s an example SQL code to insert multiple rows into the “employees” table using a subquery:

INSERT INTO employees (id, name, email, salary)
 SELECT id, name, email, salary
 FROM temp_employees;

In this example, we are selecting data from a temporary table called “temp_employees” and inserting it into the “employees” table. The temporary table must have the same columns as the “employees” table.

Inserting multiple rows in a single SQL query is a useful technique that can save time and make your code more efficient. Whether you’re inserting a few rows or thousands of rows, using the right SQL code can make the process much easier. Remember to always test your code before using it in a production environment.

Related tutorials