Insert Multiple Row in SQL

To insert multiple rows into a table in SQL, you can use the INSERT INTO statement with a single VALUES clause containing multiple value sets, or you can use the INSERT INTO statement with a SELECT statement to retrieve the values from another table or construct them on the fly.

Here's how you can insert multiple rows using the VALUES clause:

INSERT INTO table_name (column1, column2, ...)
VALUES
    (value1_1, value1_2, ...),
    (value2_1, value2_2, ...),
    ...;

• table_name: The name of the table where you want to insert the data.
• (column1, column2, ...): Optional. Specifies the columns into which you want to insert data. If omitted, values will be inserted into all columns in the order they are defined in the table.
• VALUES: Keyword indicating the beginning of the list of values to be inserted.
• (value1_1, value1_2, ...): The values for the first row to be inserted.
• (value2_1, value2_2, ...): The values for the second row to be inserted. And so on for additional rows.

INSERT INTO employees (employee_id, first_name, last_name, hire_date)
VALUES
    (1, 'John', 'Doe', '2024-02-06'),
    (2, 'Jane', 'Smith', '2024-02-07'),
    (3, 'Michael', 'Johnson', '2024-02-08');

This SQL statement inserts three rows into the employees table with the specified values for each row.

Alternatively, you can use the INSERT INTO statement with a SELECT statement to insert multiple rows from another table or construct the values dynamically. The SELECT statement can retrieve values from any valid data source, including literal values, functions, or other tables.

INSERT INTO employees (employee_id, first_name, last_name, hire_date)
        SELECT 4, 'Emily', 'Brown', '2024-02-09'
        UNION ALL
        SELECT 5, 'William', 'Jones', '2024-02-10';

This SQL statement inserts two rows into the 'employees' table by selecting values directly from the SELECT statement. Each SELECT statement within the UNION ALL construct represents a row to be inserted.