SQL INSERT Multiple Rows: A Time-Saving Technique

SQL INSERT Multiple Rows

When working with databases, you may need to add several records to a table at the same time. Creating a separate INSERT statement for every record can be repetitive and inefficient. SQL makes this easier by allowing multiple rows to be inserted with a single INSERT query.

In this article, we will learn how to insert multiple records into a table using SQL, with practical examples in MySQL.

Complete Advance AI Topics: Click Here
SQL Tutorial: Click Here

Example 1: Inserting Multiple Records into an employees Table

First, select the database in which you want to create the table:

mysql> USE company_db;

Next, create an employees table inside the company_db database:

mysql> CREATE TABLE employees (
    EmployeeID INT, 
    EmployeeName VARCHAR(30), 
    Salary INT, 
    Department VARCHAR(20), 
    JoiningDate DATE
);

Inserting Multiple Records

Rather than running an INSERT query separately for every employee, multiple records can be added using one statement:

mysql> INSERT INTO employees (EmployeeID, EmployeeName, Salary, Department, JoiningDate) 
VALUES 
    (1, "Amit Sharma", 75000, "HR", "2020-06-15"), 
    (2, "Neha Verma", 82000, "Finance", "2019-09-10"), 
    (3, "Rajesh Kumar", 91000, "IT", "2018-03-25"), 
    (4, "Sanya Gupta", 56000, "Marketing", "2021-05-12"), 
    (5, "Vikas Singh", 70000, "Operations", "2017-11-30"), 
    (6, "Pooja Mehta", 65000, "HR", "2022-01-05"), 
    (7, "Rohan Malhotra", 88000, "Finance", "2016-08-14"), 
    (8, "Swati Yadav", 72000, "Sales", "2019-04-21"), 
    (9, "Deepak Joshi", 93000, "IT", "2015-12-07"), 
    (10, "Anjali Chauhan", 60000, "Admin", "2023-02-18");

Verifying Inserted Records

After inserting the records, you can use the SELECT statement to verify the data:

mysql> SELECT * FROM employees;

The query will return the records stored in the employees table, allowing you to confirm that all ten rows were inserted successfully.

Example 2: Inserting Multiple Records into an inventory Table

Now, let’s create another table called inventory to store information about product stock:

mysql> CREATE TABLE inventory (
    ItemID INT, 
    ItemName VARCHAR(30), 
    Quantity INT, 
    Price DECIMAL(10,2), 
    AddedDate DATE
);

Inserting Multiple Records

Multiple products can be inserted into the inventory table with a single INSERT statement:

mysql> INSERT INTO inventory (ItemID, ItemName, Quantity, Price, AddedDate) 
VALUES 
    (1, "Laptop", 5, 55000.00, "2022-07-01"), 
    (2, "Mouse", 20, 500.00, "2022-07-02"), 
    (3, "Keyboard", 15, 1500.00, "2022-07-03"), 
    (4, "Monitor", 7, 12000.00, "2022-07-04"), 
    (5, "Printer", 4, 8000.00, "2022-07-05"), 
    (6, "Webcam", 10, 2500.00, "2022-07-06"), 
    (7, "Headphones", 12, 2000.00, "2022-07-07"), 
    (8, "Speaker", 6, 3000.00, "2022-07-08"), 
    (9, "Router", 8, 4500.00, "2022-07-09"), 
    (10, "External Hard Drive", 5, 6000.00, "2022-07-10");

Verifying Inserted Records

To check whether the inventory records were inserted correctly, run the following query:

mysql> SELECT * FROM inventory;

This displays the records stored in the inventory table and lets you verify that all ten products were added successfully.

Download New Real Time Projects :- Click here

Conclusion

Using one INSERT statement to add multiple rows is a convenient way to insert several records into a database. It avoids the need to execute individual queries for every row and makes bulk data insertion easier to manage.

Here are the main points to remember:

• Use one INSERT statement when you need to add multiple records.
• Use SELECT to verify that the records were inserted correctly.
• Multiple-row insertion can make database operations more efficient by reducing the number of individual queries.

By using this approach, developers can insert multiple records efficiently while keeping their SQL operations simple and organized.

Stay tuned for more database tips and tricks!

Keywords: sql insert multiple rows, sql insert multiple rows query, sql insert multiple rows from select, sql insert multiple rows from list, sql insert multiple rows oracle, sql insert multiple rows into temp table, sql insert multiple rows postgres, sql insert multiple rows from csv, sql insert multiple rows if not exists, sql insert multiple rows from another table, mysql insert multiple rows, insert multiple rows in sql w3schools, azure sql insert multiple rows, how to insert multiple values in a single column in sql, insert into multiple rows