Multiple inserts in Oracle SQL like MySQL?

Question

INSERT INTO billing
  (billing.emp_id, billing.billing_date, billing.billing_flag)
VALUES
  (1001, '2017-06-08', true),
  (1002, '2017-06-08', true) ON DUPLICATE KEY UPDATE billing_date =
VALUES
  (billing_date), billing_flag =
VALUES
  (billing_flag);

I use this query on MySQL for multiple inserts. Is this possible in Oracle?

Possible duplicate of [Oracle: ON DUPLICATE KEY UPDATE](https://stackoverflow.com/questions/17254018/oracle-on-duplicate-key-update) — Raymond Nijland, Jul 14 '17 at 11:09
By 'update' I think you mean 'insert'. These are two different things in SQL. I have edited the question to reflect this. Please edit it if that is not what you are asking. — William Robertson, Jul 15 '17 at 08:16

score 1 · Answer 1 · answered Jul 14 '17 at 11:10

I assume you're using oracle. If you want to INSERT multiple rows in one go, you can do it like this:

INSERT ALL
  INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
  INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
  INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
SELECT * FROM dual;

score 1 · Answer 2 · answered Jul 14 '17 at 14:21

The example you posted will not work like that in PL/SQL.
If you would like to insert multiple columns in the same table you can do 2 things. You could use multiple insert into statements.

For example:

INSERT INTO Billing(Emp_Id, Biling_Date) VALUES ('1001', '2017-06-08');
INSERT INTO Billing(Emp_Id, Biling_Date) VALUES ('1002', '2017-06-08');

Or you could make a procedure where do the insert in the table, and then call that procedure. That way you don't have to constantly write out all column names.

For example:

CREATE OR REPLACE PROCEDURE InsertBilling (nEmp_Id IN NUMBER, vBiling_Date IN VARCHAR2)
AS 
BEGIN 
        INSERT INTO Billing(Emp_Id, Biling_Date)
        VALUES (nEmp_Id, vBiling_Date);

        COMMIT;
END; 
/

BEGIN
        InsertBilling('1001', '2017-06-08');
        InsertBilling('1002', '2017-06-08');
END;
/

Multiple inserts in Oracle SQL like MySQL?

2 Answers2