In SQL, you can insert multiple rows into a table in a single query using the INSERT INTO statement followed by a VALUES clause that contains multiple sets of values. The syntax typically looks like this:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES
(value1a, value1b, value1c, ...),
(value2a, value2b, value2c, ...),
(value3a, value3b, value3c, ...);
Example
Suppose you have a table named employees with three columns: first_name, last_name, and email. You can insert multiple rows as follows:
INSERT INTO employees (first_name, last_name, email)
VALUES
('John', 'Doe', 'john.doe@example.com'),
('Jane', 'Smith', 'jane.smith@example.com'),
('Emily', 'Jones', 'emily.jones@example.com');
Points to Consider
1. Data Types: Ensure that the values you are inserting match the data types of the columns defined in the table schema.
2. Null Values: If a column allows NULL values and you don’t want to provide a value for that column in some rows, you can use NULL or omit it in the values if not required.
3. Aggregate Rows: Some database management systems may have limits on the number of rows you can insert in a single statement, so be mindful of those limits. Always consult the documentation for specific limitations regarding your database system.
4. Transactions: For bulk inserts, consider wrapping your INSERT statement in a transaction to ensure data integrity.
This method is simple and efficient for inserting multiple records at once, reducing the number of separate database round trips compared to executing multiple INSERT statements.