Oracle provides the ability to insert data into a table using the results of a query, known as an "INSERT INTO SELECT" statement. This can be useful when you want to combine data from multiple tables into one table, or when you want to insert a subset of data from one table into another table.
Here is an example of an "INSERT INTO SELECT" statement that copies all the data from the "employees" table into a new table called "employees_backup":
INSERT INTO employees_backup
SELECT * FROM employees;
In this example, the SELECT statement retrieves all the columns and rows from the "employees" table and the INSERT INTO statement inserts them into the "employees_backup" table.
You can also use "INSERT INTO SELECT" to insert only specific columns from a table. For example, the following statement will insert only the "first_name" and "last_name" columns from the "employees" table into the "employees_backup" table:
INSERT INTO employees_backup (first_name, last_name)
SELECT first_name, last_name FROM employees;
You can also use "INSERT INTO SELECT" to insert data from multiple tables into a single table. The following example shows how to insert data from the "employees" and "departments" tables into a new table called "employees_departments":
INSERT INTO employees_departments
SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
JOIN departments ON employees.department_id = departments.department_id;
In this example, the SELECT statement retrieves the "first_name", "last_name", and "department_name" columns from both the "employees" and "departments" tables, and the INSERT INTO statement inserts them into the "employees_departments" table. The JOIN clause is used to combine the data from the two tables based on the "department_id" column.
You can also use a subquery to filter the data being inserted. Here is an example of using "INSERT INTO SELECT" statement with a subquery:
INSERT INTO employees_backup
SELECT * FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'IT');
In this example, the subquery retrieves the department_id from the "departments" table where the department_name is 'IT' and the main query uses that department_id to filter the employees table and insert into employees_backup table.
As you can see, the "INSERT INTO SELECT" statement is a powerful tool for inserting data into a table based on the results of a query. It allows you to combine data from multiple tables, insert only specific columns, and filter the data being inserted using a subquery.
Note: If the target table is not exist, you need to create the table first, before running the insert statement.
In addition to the basic "INSERT INTO SELECT" statement, Oracle also provides some advanced options that can be useful in specific situations.
One of these options is the "INSERT ALL" statement, which allows you to insert multiple sets of data into a table with a single statement. Here is an example:
INSERT ALL
INTO employees_backup (first_name, last_name) VALUES ('John', 'Doe')
INTO employees_backup (first_name, last_name) VALUES ('Jane', 'Doe')
INTO employees_backup (first_name, last_name) VALUES ('Bob', 'Smith')
SELECT * FROM dual;
In this example, the INSERT ALL statement is used to insert three sets of data into the "employees_backup" table. The SELECT statement is used to select a single row from the "dual" table, which is a one-row, one-column table that is often used in Oracle as a placeholder for a SELECT statement.
Another advanced option is the "MERGE INTO" statement, which allows you to insert data into a table while also updating existing data. Here is an example:
MERGE INTO employees_backup tgt
USING (SELECT employee_id, first_name, last_name FROM employees) src
ON (tgt.employee_id = src.employee_id)
WHEN MATCHED THEN
UPDATE SET tgt.first_name = src.first_name, tgt.last_name = src.last_name
WHEN NOT MATCHED THEN
INSERT (employee_id, first_name, last_name)
VALUES (src.employee_id, src.first_name, src.last_name);
In this example, the "MERGE INTO" statement is used to merge data from the "employees" table into the "employees_backup" table. The "USING" clause specifies the source table and the "ON" clause specifies the join condition between the source and target tables. The "WHEN MATCHED THEN" clause specifies the action to be taken when a match is found, in this case updating the existing data. The "WHEN NOT MATCHED THEN" clause specifies the action to be taken when no match is found, in this case inserting new data.
It's also worth noting that Oracle supports the RETURNING INTO clause which allows you to return the values from the inserted rows. Here is an example:
INSERT INTO employees_backup (first_name, last_name)
VALUES ('John', 'Doe')
RETURNING employee_id, first_name, last_name INTO :id, :fname, :lname;
This statement will insert a new row into the "employees_backup" table and return the values of the employee_id, first_name, and last_name columns of the inserted row into the variables id, fname, and lname, respectively.
In conclusion, the "INSERT INTO SELECT" statement is a powerful tool for inserting data into a table based on the results of a query. It also provides advanced options such as "INSERT ALL" and "MERGE INTO" statements that can be used in specific situations. And the RETURNING INTO clause which allows you to return the values from the inserted rows.
Popular questions
- How do I insert data from one table into another table in Oracle?
You can use the "INSERT INTO SELECT" statement to insert data from one table into another table in Oracle. For example, the following statement will insert all the data from the "employees" table into a new table called "employees_backup":
INSERT INTO employees_backup
SELECT * FROM employees;
- Can I insert only specific columns from a table using the "INSERT INTO SELECT" statement?
Yes, you can use the "INSERT INTO SELECT" statement to insert only specific columns from a table. For example, the following statement will insert only the "first_name" and "last_name" columns from the "employees" table into the "employees_backup" table:
INSERT INTO employees_backup (first_name, last_name)
SELECT first_name, last_name FROM employees;
- Can I insert data from multiple tables into a single table using the "INSERT INTO SELECT" statement?
Yes, you can use the "INSERT INTO SELECT" statement to insert data from multiple tables into a single table. For example, the following example shows how to insert data from the "employees" and "departments" tables into a new table called "employees_departments":
INSERT INTO employees_departments
SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
JOIN departments ON employees.department_id = departments.department_id;
- Can I use a subquery to filter the data being inserted using the "INSERT INTO SELECT" statement?
Yes, you can use a subquery to filter the data being inserted using the "INSERT INTO SELECT" statement. Here is an example:
INSERT INTO employees_backup
SELECT * FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'IT');
In this example, the subquery retrieves the department_id from the "departments" table where the department_name is 'IT' and the main query uses that department_id to filter the employees table and insert into employees_backup table.
- Can I use the "INSERT INTO SELECT" statement to update existing data?
No, The "INSERT INTO SELECT" statement is used to insert new data into a table. If you want to update existing data, you can use the "UPDATE" statement or the "MERGE INTO" statement. The "MERGE INTO" statement allows you to insert data into a table while also updating existing data. Here is an example:
MERGE INTO employees_backup tgt
USING (SELECT employee_id, first_name, last_name FROM employees) src
ON (tgt.employee_id = src.employee_id)
WHEN MATCHED THEN
UPDATE SET tgt.first_name = src.first_name, tgt.last_name = src.last_name
WHEN NOT MATCHED THEN
INSERT (employee_id, first_name, last_name)
VALUES (src.employee_id, src.first_name, src.last_name);
In this example, the "MERGE INTO" statement is used to merge data from the "employees" table into the "
Tag
OracleSQL
