CODEWITHSUNDEEP
oracle-database
Gs Prep
Gs Prep

Reputation: 11

Why am i not able to insert multiple values at a time by this code?

I have created the table successfully but not able to insert multiple values by this code. What is the mistake. Oracle Code

CREATE TABLE sales (
    customer_id VARCHAR2(4)
    ,order_date DATE
    ,product_id VARCHAR2(5)
    );

INSERT INTO sales
VALUES (
    'A'
    ,'01-JAN-2021'
    ,'2'
    );(
    'A'
    ,'07-JAN-2021'
    ,'2'
    );(
    'A'
    ,'10-JAN-2021'
    ,'3'
    );(
    'A'
    ,'11-JAN-2021'
    ,'3'
    );(
    'A'
    ,'11-JAN-2021'
    ,'3'
    );(
    'B'
    ,'01-JAN-2021'
    ,'2'
    );(
    'B'
    ,'02-JAN-2021'
    ,'2'
    );(
    'B'
    ,'04-JAN-2021'
    ,'1'
    );(
    'B'
    ,'11-JAN-2021'
    ,'1'
    );(
    'B'
    ,'16-JAN-2021'
    ,'3'
    );(
    'B'
    ,'01-FEB-2021'
    ,'3'
    );(
    'C'
    ,'01-JAN-2021'
    ,'3'
    );(
    'C'
    ,'01-JAN-2021'
    ,'3'
    );(
    'C'
    ,'07-JAN-2021'
    ,'3'
    );

Upvotes: 1

Views: 1039

Answers (1)

Littlefoot
Littlefoot

Reputation: 143063

Why? because that's invalid syntax in Oracle.

One option is this:

SQL> INSERT INTO sales VALUES ('A', DATE '2021-01-01', '2');

1 row created.

SQL> INSERT INTO sales VALUES ('A', DATE '2021-01-07', '2');

1 row created.

SQL> INSERT INTO sales VALUES ('A', DATE '2021-01-10', '3');

1 row created.

etc.

Note that you shouldn't insert strings into date datatype column; I used date literal; you could use TO_DATE with appropriate format model.

Another option is e.g.

SQL> INSERT ALL
  2    INTO sales VALUES ('A', DATE '2021-01-01', '2')
  3    INTO sales VALUES ('A', DATE '2021-01-07', '2')
  4  SELECT * FROM DUAL;

2 rows created.

or

SQL> INSERT INTO sales
  2     SELECT 'A', DATE '2021-01-01', '2' FROM DUAL
  3     UNION ALL
  4     SELECT 'A', DATE '2021-01-07', '2' FROM DUAL;

2 rows created.

Upvotes: 5

Related Questions