Member Avatar for cutedipti

hello!

As i am trying to insert multiple rows using following command

insert into customer(c_id,c_name,c_city,c-street)
             values('C101','Hayes','Downtown','Main'),
                       ('C102','Willum','Perryridge','North'),
                       ('c103','Smit','Brooklyn','Park');

It always gives me an error that
[TEX]
ERROR at line 2:
command not properly ended
[/TEX]


Please tell me why this error is occuring & what's solution to it?
I am using ORACLE database for execution of this query
i.e. oracle work screen iSQL*Plus.

  • 4 Contributors
  • 5 Replies
  • 530 Views
  • 4 Weeks Discussion Span
  • Latest Post Latest Post by Princeomari

Dani AI

Generated

Two things caused the error in the original post: the SQL you tried (a single INSERT with multiple parenthesised row constructors) was not supported in older Oracle releases, and the column name you used, c-street, contains a hyphen so it must be a quoted identifier (or renamed) to be a valid column name. The multi-row VALUES / table-value-constructor is a recent addition to Oracle; older versions require different techniques. (docs.oracle.com)

The replies from , and point you to the right workarounds. For non‑23c Oracle use one of these practical options: run separate INSERT statements (simplest), use an INSERT ALL (unconditional multi-into form), or build a small SELECT with UNION ALL (SELECT … FROM DUAL / WITH … UNION ALL) and INSERT from that. When you need high performance for many rows, use PL/SQL bulk methods (FORALL / BULK COLLECT) or client-side batch/SQL*Loader. (docs.oracle.com)

Examples (do not copy the original c-street — either rename it to c_street or quote it):

-- INSERT ALL (works on older Oracle releases)
INSERT ALL
  INTO customer (c_id,c_name,c_city,c_street) VALUES ('C101','Hayes','Downtown','Main')
  INTO customer (c_id,c_name,c_city,c_street) VALUES ('C102','Willum','Perryridge','North')
  INTO customer (c_id,c_name,c_city,c_street) VALUES ('C103','Smit','Brooklyn','Park')
SELECT * FROM DUAL;
-- Oracle 23c+ supports the SQL-standard multi-row VALUES constructor
INSERT INTO customer (c_id,c_name,c_city,c_street)
VALUES
  ('C101','Hayes','Downtown','Main'),
  ('C102','Willum','Perryridge','North'),
  ('C103','Smit','Brooklyn','Park');

(INSERT ALL example: Oracle INSERT reference; VALUES / table-value-constructor: Oracle 23 features.) (docs.oracle.com)

Short cautions: if a column name contains punctuation or spaces always use double quotes; quoted identifiers are case‑sensitive. For bulk loads prefer FORALL or client bulk/batch APIs to reduce context switches and improve throughput. (docs.oracle.com)

Recommended Answers

All 5 Replies

Member Avatar for anubina

hello!

As i am trying to insert multiple rows using following command

insert into customer(c_id,c_name,c_city,c-street)
             values('C101','Hayes','Downtown','Main'),
                       ('C102','Willum','Perryridge','North'),
                       ('c103','Smit','Brooklyn','Park');

It always gives me an error that

ERROR at line 2:
command not properly ended

Please tell me why this error is occuring & what's solution to it?
I am using ORACLE database for execution of this query
i.e. oracle work screen iSQL*Plus.

You can`t insert multiples records in a simple INSERT statement.
For insert multiples records in one INSERT statement, you need use the INSERT as SELECT:

insert into customer(c_id,c_name,c_city,c-street)
select 'C101' as c_id,'Hayes'  as c_name,'Downtown'   as c_city,'Main'  as "c-street" from dual
select 'C102' as c_id,'Willum' as c_name,'Perryridge' as c_city,'North' as "c-street" from dual
select 'c103' as c_id,'Smit'   as c_name,'Brooklyn'   as c_city,'Park'  as "c-street" from dual;
Member Avatar for anubina

You can`t insert multiples records in a simple INSERT statement.
For insert multiples records in one INSERT statement, you need use the INSERT as SELECT:

insert into customer(c_id,c_name,c_city,c-street)
select 'C101' as c_id,'Hayes'  as c_name,'Downtown'   as c_city,'Main'  as "c-street" from dual
select 'C102' as c_id,'Willum' as c_name,'Perryridge' as c_city,'North' as "c-street" from dual
select 'c103' as c_id,'Smit'   as c_name,'Brooklyn'   as c_city,'Park'  as "c-street" from dual;

Ooooops!,

Sorry,

We need the union for the three select's

insert into customer(c_id,c_name,c_city,c-street)
select 'C101' as c_id,'Hayes'  as c_name,'Downtown'   as c_city,'Main'  as "c-street" from dual
select 'C102' as c_id,'Willum' as c_name,'Perryridge' as c_city,'North' as "c-street" from dual
select 'c103' as c_id,'Smit'   as c_name,'Brooklyn'   as c_city,'Park'  as "c-street" from dual;insert into customer(c_id,c_name,c_city,c-street)
select 'C101' as c_id,'Hayes'  as c_name,'Downtown'   as c_city,'Main'  as "c-street" from dual
UNION select 'C102' as c_id,'Willum' as c_name,'Perryridge' as c_city,'North' as "c-street" from dual
UNION select 'c103' as c_id,'Smit'   as c_name,'Brooklyn'   as c_city,'Park'  as "c-street" from dual;
Member Avatar for debasisdas

hello!

As i am trying to insert multiple rows using following command

insert into customer(c_id,c_name,c_city,c-street)
             values('C101','Hayes','Downtown','Main'),
                       ('C102','Willum','Perryridge','North'),
                       ('c103','Smit','Brooklyn','Park');

It always gives me an error that
ERROR at line 2:
command not properly ended

Please tell me why this error is occuring & what's solution to it?
I am using ORACLE database for execution of this query
i.e. oracle work screen iSQL*Plus.

The code you are using is not supported in oracle .
You need to create separate insert statements for each row of data.

Member Avatar for Princeomari

Hi,
What you can do is to insert into the customer table for each row.
hope it solves the issue for u

Member Avatar for Princeomari

Hi,
What you can do is to insert into the customer table for each row.
hope it solves the issue for u

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.