How to Insert Multiple Records into a SQL Table

Insert Multiple Records

Write a SQL query to insert multiple records into a table in a single operation.

Solution:

-- Insert multiple employee records into the "Employees" table.
INSERT INTO Employees (EmployeeID, Name, Age, Salary) -- Specify the columns to insert data into.
VALUES 
    (2, 'Berna Alder', 25, 45000), -- First record.
    (3, 'Khayri Ivo', 28, 52000), -- Second record.
    (4, 'Teppo Abel', 35, 60000); -- Third record.

Explanation:

1. Purpose of the Query :

1. The goal is to insert multiple records into the Employees table using a single INSERT INTO statement.
2. This demonstrates how to efficiently add multiple rows of data to a table without writing separate queries for each record.

2. Key Components :

1. INSERT INTO Employees : Specifies the table where the new records will be added.
2. (EmployeeID, Name, Age, Salary) : Lists the columns in which data will be inserted.
3. VALUES Clause : Contains multiple sets of values, each enclosed in parentheses and separated by commas, representing individual rows to be inserted.

3. Why use Multiple Inserts? :

1. Inserting multiple records in a single query is more efficient than executing multiple INSERT statements, especially when dealing with large datasets.
2. For example, if you need to add several employees to the database at once, this approach reduces the number of database operations.

4. Real-World Application :

1. For example, in a company database, you might use this query to add multiple employees (e.g., Berna Alder, Khayri Ivo, and Teppo Abel) with their respective details in one go.

Additional Notes:

• Inserting multiple records in a single query reduces overhead and improves performance compared to executing multiple INSERT statements.
• Scenarios where inserting multiple records is appropriate, such as:
• Bulk-loading data during initial setup or migration.
• Adding multiple users, products, or transactions in one operation.
• Important Considerations :
• Ensure that all sets of values match the data types and constraints of the specified columns.
• If the table has an auto-incrementing primary key, you may omit that column from the INSERT INTO statement.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Insert a Single Record
Next SQL Exercise: Update a Single Record.