How to use insert into select in oracle

The INSERT INTO SELECT statement is used to insert data from one table into another table. It supports inserting all columns or specific columns and can filter using conditions via WHERE clause. Data can be easily transferred from one table to another by specifying the target table, column list, and source table.

INSERT INTO SELECT usage in Oracle

Meaning:

The INSERT INTO SELECT statement will Data from one table is inserted into another table.

Syntax:

<code>INSERT INTO 
SELECT 
FROM 
[WHERE ]</code>

Parameters:

• ##:The target table to insert data into.
• : The columns to be inserted, you can specify all columns or specific columns.
• : The source table from which to obtain data.
• [WHERE ]: Optional conditions used to filter data in the source table.

Usage:

1. Insert all columns:

   If

   column If no columns are specified in list , all columns in the source table will be inserted.

   <code>INSERT INTO target_table
   SELECT *
   FROM source_table;</code>

2. Insert specific columns:

   If specific columns are specified in the

   column list, only the specified columns will be inserted .

   <code>INSERT INTO target_table (col1, col2)
   SELECT col1, col2
   FROM source_table;</code>

3. Using conditions:

   WHERE clause can be used to filter the data in the source table and insert only those that meet the conditions OK.

   <code>INSERT INTO target_table
   SELECT *
   FROM source_table
   WHERE column_name > 10;</code>

Example:

Suppose we have the following two tables:

<code>source_table:
+----+----------+
| id | name      |
+----+----------+
| 1  | John Doe  |
| 2  | Jane Smith |
| 3  | Mary Jones |
+----+----------+

target_table:
+----+----------+
| id | name      |
+----+----------+
| 4  | Bob Smith  |
| 5  | Sue Brown  |
+----+----------+</code>

To be obtained from

source_table To insert all rows into target_table, we can use the following query:

<code>INSERT INTO target_table
SELECT *
FROM source_table;</code>

After insertion,

target_table will look like this:

<code>+----+----------+
| id | name      |
+----+----------+
| 4  | Bob Smith  |
| 5  | Sue Brown  |
| 1  | John Doe  |
| 2  | Jane Smith |
| 3  | Mary Jones |
+----+----------+</code>

The above is the detailed content of How to use insert into select in oracle. For more information, please follow other related articles on the PHP Chinese website!