Introduction

In SQL, the INSERT statement is used to add new records to a table. While inserting single rows is common, inserting multiple rows in one go can be much more efficient and straightforward. This article will guide you through the process of inserting multiple rows into a table using the INSERT statement, with new examples and use cases.

Creating a Database and Table

Before inserting data, you need to have a database and a table set up. Let’s create a sample database and a table:

Creating a Database

To create a new database named ProductInventory, use the following command:

CREATE DATABASE ProductInventory;

Using the Database

Switch to the newly created database with:

USE ProductInventory;

Creating a Table

Create a table named products with columns for product ID, name, category, and price:

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10, 2)
);

Inserting Individual Rows

To insert data into the products table, use the INSERT INTO statement. Here’s how to insert individual rows:

INSERT INTO products (product_id, product_name, category, price) 
VALUES (1, 'Laptop', 'Electronics', 999.99);

INSERT INTO products (product_id, product_name, category, price) 
VALUES (2, 'Office Chair', 'Furniture', 149.99);

INSERT INTO products (product_id, product_name, category, price) 
VALUES (3, 'Wireless Mouse', 'Accessories', 29.99);

Viewing the Inserted Data

To verify the inserted data, use the following query:

SELECT * FROM products;

Inserting Multiple Rows

To insert multiple rows in a single statement, use the INSERT INTO statement with multiple VALUES tuples:

INSERT INTO products (product_id, product_name, category, price)
VALUES 
    (4, 'Smartphone', 'Electronics', 499.99),
    (5, 'Desk Lamp', 'Furniture', 39.99),
    (6, 'Bluetooth Speaker', 'Accessories', 89.99),
    (7, 'Gaming Monitor', 'Electronics', 299.99);

Viewing the Data After Insertion

Check the updated table with:

SELECT * FROM products;

Key Points

• Efficiency: Inserting multiple rows in one INSERT INTO statement is more efficient than inserting rows one by one, as it reduces the number of transactions.
• Syntax: Ensure that the columns listed in the INSERT INTO statement match the values provided in the VALUES clause.
• Use Cases: This method is particularly useful for importing large datasets or initializing tables with default data.

Conclusion

Inserting multiple rows with a single INSERT INTO statement can greatly enhance the efficiency of data operations. By using batch insertion, you can handle large volumes of data more effectively and streamline database management tasks. Practice these techniques to optimize your SQL data entry operations.

This revised blog post provides a fresh perspective with new examples, focusing on different types of data and scenarios.