How to Insert Data into Multiple Database Tables Simultaneously?

Inserting Data Across Multiple Database Tables

Problem: How to efficiently insert data into multiple database tables using a single operation?

Solution: While a single SQL query cannot directly insert into multiple, distinct tables, database transactions provide a reliable solution for atomic operations across multiple tables.

Implementation:

<code class="language-sql">BEGIN TRANSACTION;
INSERT INTO table1 (column1, column2, column3) VALUES ('1', '2', '3');
INSERT INTO table2 (columnA, columnB) VALUES ('bob', 'smith');
COMMIT;</code>

Explanation:

• BEGIN TRANSACTION: This statement starts a transaction, grouping multiple SQL operations into a single unit of work.
• INSERT statements: These insert data into table1 and table2 respectively.
• COMMIT: This statement commits the transaction. If all INSERT statements succeed, the changes are permanently saved; if any INSERT fails, the entire transaction is rolled back, leaving the database unchanged. This ensures data consistency.

This transactional approach ensures data integrity by either successfully inserting data into all target tables or leaving the database untouched in case of failure.

The above is the detailed content of How to Insert Data into Multiple Database Tables Simultaneously?. For more information, please follow other related articles on the PHP Chinese website!