Unconditional and Conditional with SQL

Unconditional and Conditional.

Unconditional Multitable INSERTs

Unconditional Multitable INSERTs allow you to insert the same data into multiple tables without applying any conditions. This type is straightforward and is used when you want to ensure that every record is inserted into all specified tables.

Syntax: 

INSERT ALL
    INTO table1 (column1, column2) VALUES (value1, value2)
    INTO table2 (column1, column2) VALUES (value1, value2)
    INTO table3 (column1, column2) VALUES (value1, value2)
SELECT * FROM dual;

• INSERT ALL: Initiates the multitable insertion process.
• INTO table_name (columns) VALUES (values): Specifies the table and the values to be inserted.
• SELECT * FROM dual: A dummy select statement from the dual table in Oracle, which is used to complete the INSERT statement.

Example 1: Basic Unconditional Insert

Suppose you have two tables, employees and employees_archive, and you want to insert a new employee into both tables. 

INSERT ALL
    INTO employees (emp_id, emp_name, emp_salary) VALUES (1001, 'John Doe', 60000)
    INTO employees_archive (emp_id, emp_name, emp_salary) VALUES (1001, 'John Doe', 60000)
SELECT * FROM dual;

In this example:

• The employee with ID 1001 is inserted into both employees and employees_archive tables.
• Every insertion specified in the INSERT ALL clause is executed without any conditions.

Example 2: Inserting Multiple Rows Unconditionally

If you need to insert different records into multiple tables: 

INSERT ALL
    INTO employees (emp_id, emp_name) VALUES (1001, 'Alice Smith')
    INTO employees (emp_id, emp_name) VALUES (1002, 'Bob Brown')
    INTO employees_archive (emp_id, emp_name) VALUES (1001, 'Alice Smith')
    INTO employees_archive (emp_id, emp_name) VALUES (1002, 'Bob Brown')
SELECT * FROM dual;

Here:

• Both Alice and Bob are inserted into both employees and employees_archive tables.

Conditional Multitable INSERTs

Conditional Multitable INSERTs allow you to insert data into different tables based on specific conditions. This type is useful when you want to route records to different tables depending on their attributes.

Syntax: 

INSERT ALL
    WHEN condition1 THEN
        INTO table1 (column1, column2) VALUES (value1, value2)
    WHEN condition2 THEN
        INTO table2 (column1, column2) VALUES (value1, value2)
    ...
SELECT * FROM dual;

• WHEN condition THEN: Specifies the condition for inserting into a particular table.
• INTO table_name (columns) VALUES (values): Specifies the table and values to be inserted if the condition is met.

Example 1: Conditional Insert Based on Employee Salary

Assume you want to insert employees into different tables based on their salary. 

INSERT ALL
    WHEN salary > 50000 THEN
        INTO high_salary_employees (emp_id, emp_name, salary) VALUES (emp_id, emp_name, salary)
    WHEN salary <= 50000 THEN
        INTO regular_employees (emp_id, emp_name, salary) VALUES (emp_id, emp_name, salary)
    SELECT emp_id, emp_name, salary FROM employees;

In this example:

• Employees with a salary greater than 50,000 are inserted into high_salary_employees.
• Employees with a salary of 50,000 or less are inserted into regular_employees.

Example 2: Conditional Insertion Based on Employee Status

Consider you have two tables: active_employees and inactive_employees. You want to insert records based on the employee’s status. 

INSERT ALL
    WHEN status = 'Active' THEN
        INTO active_employees (emp_id, emp_name, status) VALUES (emp_id, emp_name, status)
    WHEN status = 'Inactive' THEN
        INTO inactive_employees (emp_id, emp_name, status) VALUES (emp_id, emp_name, status)
    SELECT emp_id, emp_name, status FROM employees;

In this scenario:

• Employees with status ‘Active’ are inserted into active_employees.
• Employees with status ‘Inactive’ are inserted into inactive_employees.

Additional Considerations

• Performance: Using conditional multitable INSERTs can be more efficient than running multiple separate INSERT statements because it reduces the number of queries and transactions.
• Data Integrity: Ensure that conditions and values are correctly specified to avoid unexpected data distribution or integrity issues.
• Error Handling: Implement appropriate error handling and transaction management to maintain data consistency, especially in complex scenarios.

Summary

Unconditional Multitable INSERTs insert the same data into all specified tables without conditions, making them simple and straightforward for parallel insertions. Conditional Multitable INSERTs provide flexibility by allowing different data to be routed to different tables based on conditions, which is useful for data segregation and categorization.