How Can I Efficiently Insert Data into Multiple Database Tables Simultaneously?

Insert multiple database tables simultaneously

In relational databases, it is often necessary to modify data in multiple tables at the same time. Although it is possible to use a separate INSERT statement, this approach is inefficient and error-prone.

Question:

Suppose the database contains three tables: Object_Table, Data_Table and Link_Table. The goal is to copy the data associated with a specific object identifier from the Data_Table and insert the corresponding records into the Data_Table and Link_Table, but with a different object identifier. While this can be accomplished using a loop and two INSERT statements per iteration, a better solution is to minimize the amount of code and potential errors.

Best solution:

In order to insert data into multiple tables in one transaction, you can use multi-table insert. This method ensures atomicity and efficiency. The following code snippet demonstrates this approach:

<code class="language-sql">BEGIN TRANSACTION;
   DECLARE @DataID int;
   INSERT INTO DataTable (Column1 ...) VALUES (....);
   SELECT @DataID = scope_identity();
   INSERT INTO LinkTable (ObjectID, DataID) VALUES (@ObjectID, @DataID);
COMMIT;</code>

Code description:

The
• BEGIN TRANSACTION statement marks the beginning of a transaction.
• The first INSERT statement inserts a record into the Data_Table and returns the identifier (@DataID) of the newly inserted row.
• The second INSERT statement uses the retrieved identifier to associate a new row in the Data_Table with the object specified in the Link_Table.
The
• COMMIT statement completes the transaction, ensuring that both insert operations succeed or both fail.

This solution does not require loops and temporary tables, making the code more concise and efficient. Additionally, it provides atomicity by guaranteeing that two insert operations act as a unit of work.

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