In SQL, a conditional insert using the INSERT...SELECT statement allows you to insert rows into a table based on specific conditions evaluated from another table (or the same table). This is particularly useful when you want to insert data that meets certain criteria without having to perform multiple steps.
Syntax
Here's the basic syntax for a conditional insert using the INSERT...SELECT statement:
INSERT INTO target_table (column1, column2, ...)
SELECT value1, value2, ...
FROM source_table
WHERE [condition];
Example
Assume you have two tables: employees and archived_employees. You want to insert records into the archived_employees table for employees who are marked as inactive in the employees table.
INSERT INTO archived_employees (employee_id, name, department)
SELECT employee_id, name, department
FROM employees
WHERE status = 'inactive';
Explanation:
1. INSERT INTO archived_employees: This specifies the table where you want to insert the data.
2. (employee_id, name, department): These are the columns in archived_employees that you are inserting data into.
3. SELECT employee_id, name, department: This selects the columns to be inserted, in this case, from the employees table.
4. FROM employees: Indicates that we are selecting data from the employees table.
5. WHERE status = 'inactive': This is the condition that determines which rows from the employees table will be inserted into the archived_employees table. Only employees with a status of 'inactive' will be selected for insertion.
Important Considerations:
- Make sure that the data types of the columns from the SELECT statement match those of the target table columns.
- If the WHERE clause results in no rows, no rows will be inserted.
- You may want to handle possible duplicates depending on your application requirements. You can add constraints or additional conditions to manage this.
Additional Example with Aggregation
If you need more complex conditions or aggregations, you can use grouping or other SQL functions in your SELECT statement. For example, inserting the average salary of employees in each department into a department_salaries table:
INSERT INTO department_salaries (department, average_salary)
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;
In this example, only departments with an average salary greater than $50,000 will be inserted into the department_salaries table.