Thursday, December 29, 2016

Insert multiple rows with a single INSERT statement


Oracle/PLSQL: Insert multiple rows with a single INSERT statement


Question: How can I insert multiple rows of explicit data in one SQL command in Oracle?
Answer: You can insert multiple rows using the following 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;

EXAMPLE #1

If you wanted to insert 3 rows into the suppliers table, you could run the following SQL statement:
INSERT ALL
   INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
   INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
   INTO suppliers (supplier_id, supplier_name) VALUES (3000, 'Google')
SELECT * FROM dual;

EXAMPLE #2

You can also insert multiple rows into multiple tables. For example, if you wanted to insert into both the suppliers and customers table, you could run the following SQL statement:
INSERT ALL
   INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
   INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
   INTO customers (customer_id, customer_name, city) VALUES (999999, 'Anderson Construction', 'New York')
SELECT * FROM dual;
This example will insert 2 rows into the suppliers table and 1 row into the customers table.

No comments:

Post a Comment

Oracle Fusion - Cost Lines and Expenditure Item link in Projects

SELECT   ccd.transaction_id,ex.expenditure_item_id,cacat.serial_number FROM fusion.CST_INV_TRANSACTIONS cit,   fusion.cst_cost_distribution_...