3

I am using oracle sql developer to insert rows in my database.

While this request is working :

INSERT INTO TABLE ( USERID, USERNAME) VALUES (1,"ok1")

The second one (when I am trying to insert multiple rows)is not working:

INSERT INTO TABLE ( USERID, USERNAME) VALUES (1,"ok1"),(2,"ok2")

I am getting this error :

Erreur SQL : ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
user2443476
  • 1,935
  • 9
  • 37
  • 66
  • 1
    that's mysql extended inserted syntax. it doesn't work anywhere but mysql. – Marc B Oct 28 '14 at 15:16
  • 2
    @MarcB: it's also supported in **SQL Server** in versions **2008** and newer – marc_s Oct 28 '14 at 15:18
  • @marc_b: it's actually ANSI SQL, not tied to specific DBMS - but not all DBMS support it. And Oracle is one of them. –  Oct 28 '14 at 15:18

4 Answers4

7

You could use INSERT ALL statement. For example:

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;
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
1

Oracle does not support multi-row inserts. You need to write one insert per row:

INSERT INTO TABLE ( USERID, USERNAME) VALUES (1,'ok1');
INSERT INTO TABLE ( USERID, USERNAME) VALUES (2,'ok2');

Additionally: string literals need to be enclosed in single quotes in SQL. Double quotes are for identifiers. "ok1" is a column name, 'ok1' is a string constant.

0

See this recent post for some other ways to enter test data as well: Insert same data multiple times

Community
  • 1
  • 1
Gary_W
  • 9,933
  • 1
  • 22
  • 40
0

If you are not worried about SQL injection then run the following :

BEGIN
INSERT INTO TABLE ( USERID, USERNAME) VALUES (1,'ok1');
INSERT INTO TABLE ( USERID, USERNAME) VALUES (2,'ok2');
END;
Jubba
  • 11
  • 1