How to Insert Data into Multiple Tables using Transactions?

Insert Data in a Transaction

Write a SQL query to insert data into multiple tables within a transaction, rolling back if any part fails.

Solution:

-- Start a transaction to insert data into multiple tables.
BEGIN TRANSACTION; -- Begin the transaction.

INSERT INTO Employees (EmployeeID, Name, Age, Salary) 
VALUES (1, 'Alice Johnson', 30, 50000); -- Insert into Employees table.

INSERT INTO Departments (DepartmentID, DepartmentName) 
VALUES (1, 'HR'); -- Insert into Departments table.

-- Simulate an error to trigger rollback.
IF @@ERROR <> 0 
BEGIN
    ROLLBACK TRANSACTION; -- Rollback if any error occurs.
    PRINT 'Transaction rolled back due to an error.';
END
ELSE
BEGIN
    COMMIT TRANSACTION; -- Commit if no errors occur.
    PRINT 'Transaction committed successfully.';
END

Explanation:

1. Purpose of the Query :

1. The goal is to insert data into multiple tables (Employees and Departments) within a single transaction.
2. If any part of the transaction fails, the entire operation is rolled back to maintain data consistency.

2. Key Components :

1. BEGIN TRANSACTION : Starts the transaction.
2. ROLLBACK TRANSACTION : Undoes all changes if an error occurs.
3. COMMIT TRANSACTION : Saves all changes if no errors occur.
4. @@ERROR : Checks for errors during execution.

3. Why use Transactions? :

1. Transactions ensure that operations are atomic (all-or-nothing), maintaining data integrity when working with multiple tables.

4. Real-World Application :

1. For example, in a company database, you might use this query to add a new employee and their department simultaneously, ensuring both inserts succeed or fail together.

Additional Notes:

• Transactions are critical for maintaining data consistency in relational databases.
• Scenarios where transactions are appropriate include:
• Inserting related data across multiple tables.
• Ensuring that partial updates do not leave the database in an inconsistent state.
• Important Considerations :
• Always test transactions on a small dataset before applying them to production data.
• Be cautious with long-running transactions, as they can lock resources and impact performance.

For more Practice: Solve these Related Problems:

• Write a SQL query to insert data into three tables (Customers, Orders, and Payments) within a single transaction, rolling back if any part fails.
• Write a SQL query to insert a new product and its corresponding supplier information in two different tables using a transaction. Rollback if either insert fails.
• Write a SQL query to transfer money between two accounts in a banking system using a transaction. Ensure that both updates succeed or fail together.
• Write a SQL query to add a new employee and assign them to a department within a transaction. Rollback if the department does not exist.

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

Previous SQL Exercise: SQL Transactions Concurrency Control Exercises Home
Next SQL Exercise: Demonstrate Isolation Level READ COMMITTED.