Inserting multiple records by using the Oracle insert Syntax ____oracle

Q: How can I insert multiple records with only one insert in Oracle? In addition to writing five inserts to insert five records into a table, I prefer to use a separate statement to perform the insertion.


A: According to the information on the psoug.org, an INSERT statement can add one or more records to any table in a relational database. In order for the user to insert a record into a table, the table must be in the user's own mode or the user must have permission to insert the object on this table.

If you are using Oracle 10g or later, you can use Insert all to insert multiple data from multiple tables into a table:

INSERT All

into table (Column1, Column2,,,)

VALUES (List of values)

SELECT ....

From Table1, table2,

WHERE ...;

If you insert data from a spreadsheet or comma-delimited file, you can either create a temporary table or create an external table to load the data.

At the same time, you can also choose the following methods:

INSERT into table_name (column_1, column_2, ..., column_n)

SELECT value_1, value_2, ..., value_n

From ...

In addition, you can insert multiple records in a separate step using the following Oracle Insert syntax:

INSERT all into MyTable (Column1, Column2, Column3) VALUES (' val1.1 ', ' val1.2 ', ' val1.3 ') into MyTable (Column1, Column2, Column3 values (' val2.1 ', ' val2.2 ', ' val2.3 ') into MyTable (Column1, Column2, Column3) VALUES (' val3.1 ', ' val3.2 ', ' Val3 '). 3 ') SELECT * from dual;

You can also insert multiple values into multiple tables by using a command similar to the following:

INSERT all in product (Product_id,product_name) VALUES (1000, ' Disc ') into product (product_id, Product_Name) VALUES (20 , ' floppy ') into customers (customer_id, Customer_name, city) VALUES (999999, ' Anderson construction ', ' New York ') Selec T * from dual;