SQL INSERT Multiple Rows: A Time-Saving Technique

SQL INSERT Multiple Rows

When working with databases, developers often need to insert multiple records into a table. Writing separate INSERT statements for each record is not only tedious but also time-consuming. Thankfully, SQL provides a way to insert multiple rows in a single query, making database operations more efficient.

In this article, we will explore how to insert multiple records at once using SQL, with practical examples using MySQL. Let’s dive in!

Complete Python Course with Advance topics:-Click Here
SQL Tutorial :-Click Here

Example 1: Inserting Multiple Records into an `employees` Table

Before inserting data, we must select the database where we want to create the table:

mysql> USE company_db;

Now, let’s create an employees table in the company_db database:

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

Inserting Multiple Records

Instead of inserting one row at a time, we can insert multiple records using a single query:

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

To check whether the records have been inserted successfully, use the SELECT statement:

mysql> SELECT * FROM employees;

The result will display all ten inserted records, confirming the success of the operation.

Example 2: Inserting Multiple Records into an `inventory` Table

Let’s create another table named inventory for managing product stock:

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

Inserting Multiple Records

Now, let’s insert multiple records into the inventory table in one go:

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 confirm that the records have been inserted successfully, execute:

mysql> SELECT * FROM inventory;

This will display all ten inserted records, ensuring the data is correctly stored.

Download New Real Time Projects :-Click here
Complete Advance AI topics:- CLICK HERE

Conclusion

Using a single INSERT statement to insert multiple rows significantly reduces the number of queries executed and improves database performance. This technique is particularly useful when dealing with bulk data inserts.

Key takeaways:

Use a single INSERT statement for multiple records to save time.
Always verify inserted records using SELECT.
This method enhances database performance and reduces query execution time.

By following this approach, developers can efficiently manage large datasets while keeping SQL operations optimized.

Stay tuned to UpdateGadh for more database tips and tricks!

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

Post Views: 683