Insert All - (Oracle Query)

The INSERT ALL statement in Oracle SQL is used to insert multiple rows into one or more tables using a single SQL statement. This can be done by specifying the columns and their values for each row that needs to be inserted. This is useful when you need to insert multiple rows into a table with similar data.

Syntax

INSERT ALL 
  INTO table1 (col1, col2, col3) VALUES (val1, val2, val3)
  INTO table2 (col1, col2, col3) VALUES (val4, val5, val6)
  INTO table3 (col1, col2, col3) VALUES (val7, val8, val9)
SELECT * FROM dual;

Here, table1, table2, and table3 are the names of the tables where the data needs to be inserted. col1, col2, and col3 are the column names in the tables, and val1, val2, val3, val4, val5, val6, val7, val8, and val9 are their respective values.

Example

INSERT ALL 
  INTO employees (employee_id, first_name, last_name) VALUES (101, 'John', 'Doe')
  INTO employees (employee_id, first_name, last_name) VALUES (102, 'Jane', 'Doe')
  INTO employees (employee_id, first_name, last_name) VALUES (103, 'David', 'Jones')
SELECT * FROM dual;

Output

This will insert three rows of data into the employees table with employee_id, first_name, and last_name columns.

Explanation

In the above example, we are inserting three rows of data into the employees table with employee_id, first_name, and last_name columns. The INSERT ALL statement allows us to insert multiple rows into the table in a single statement by specifying the values for each column. The SELECT * FROM dual statement is used to satisfy the syntax requirement, but it does not actually retrieve any data.

Use

The INSERT ALL statement can be used when you need to insert similar data into multiple tables in a single SQL statement. This can save time and improve performance when compared to multiple INSERT statements.

Important Points

• The INSERT ALL statement is used to insert multiple rows of data into one or more tables using a single SQL statement in Oracle SQL.
• The statement allows you to specify columns and their respective values for each row that needs to be inserted.
• The SELECT * FROM dual statement is used to satisfy the syntax requirement but does not actually retrieve any data.

Summary

In summary, the INSERT ALL statement is a powerful feature in Oracle SQL that can be used to insert multiple rows of data into one or more tables using a single SQL statement. This feature can save you time and improve performance when compared to multiple INSERT statements.