The best way to do a multi-row insert in Oracle is to use the INSERT ALL statement. This statement allows you to insert multiple rows into a table or multiple tables at once using a single command. The syntax for the INSERT ALL statement is as follows:
INSERT ALL
INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...)
INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...)
INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...)
...
SELECT * FROM dual;
You can also use the FORALL statement to insert multiple rows in a single command. The syntax for the FORALL statement is as follows:
FORALL i IN 1..n
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (list_of_values(i));
Please note that it is also possible to use the "INSERT INTO SELECT" statement to insert multiple rows from one table to another in a single command. You can also use the "INSERT INTO SELECT" statement to insert multiple rows from one table to another in a single command. The syntax for the statement is as follows:
INSERT INTO table1 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table2
WHERE ...;
Another way to perform multi-row insert in Oracle is by using the BULK COLLECT and FORALL statements together. This method allows you to insert large number of rows into a table at once. The basic syntax for this method is as follows:
DECLARE
TYPE my_table_type IS TABLE OF table_name%ROWTYPE;
my_table my_table_type;
BEGIN
SELECT ...
BULK COLLECT INTO my_table
FROM ...;
FORALL i IN 1..my_table.COUNT
INSERT INTO table_name
VALUES my_table(i);
END;
It is important to note that it is also possible to use the "MERGE" statement to insert or update multiple rows in a single command. Please keep in mind that the best option depends on the specific requirements of your use case and the amount of data you need to insert
0 Komentar