Member Avatar for cutedipti

hello,
I am not getting which SQL statement should be used to insert multiple data at a same time without writing insert into... again and again.
suppose whenever i want to insert data into 'customer' table then i may write statement as

insert into customer(c_id,c_name,c_city,c-street)
             values('C101','Hayes','Downtown','Main')

But it will create only one row and for next row i have to repeate the same things.

Is there any query which will directly ask me to enter many rows?

  • 4 Contributors
  • 4 Replies
  • 141 Views
  • 2 Weeks Discussion Span
  • Latest Post Latest Post by anubina

Dani AI

Generated

The posts already point at the right high‑level choices (batch/load, INSERT…SELECT, or a PL/SQL loop — see , and ). The practical choice depends on where the data lives and how many rows you need to move. Below are clear, actionable options and a compact PL/SQL example for bulk insertion.

If the source is an application (JDBC/ODBC), prefer client‑side batching or array binds (avoid a separate round‑trip per row). In PL/SQL, use collection binding with FORALL rather than row‑by‑row INSERTs — it reduces context switches and is much faster for thousands of rows.

A minimal PL/SQL bulk example (use it as a pattern; change names/types to match your table):

declare
  type t_str is table of varchar2(100);
  ids   t_str := t_str('C101','C102','C103');
  names t_str := t_str('Hayes','Miller','Smith');
begin
  forall i in 1..ids.count
    insert into my_customers (cust_id, cust_name) values (ids(i), names(i));
  commit;
end;
/

If the data is in files, use a bulk loader or external table and move rows with a single SQL statement from that external table — this is far better than repeated single INSERTs. For very large loads, consider direct‑path loads (APPEND), parallel DML, temporarily disabling nonessential indexes/triggers, and rebuilding afterwards to improve insert throughput.

If you must handle existing rows (upsert), MERGE is generally simpler and more maintainable than advanced MODEL usage. Always control transaction size (commit every N rows only if necessary), monitor undo/redo consumption, and test on a copy of the target schema to verify performance and constraints handling.

Recommended Answers

All 4 Replies

Member Avatar for debasisdas

what is the source of your data ?

if it is in a flat file you can use sql loader also try to use INSERT ALL statement.

Member Avatar for cgyrob

If you are inserting data from other tables you can use a select statment

Insert into customer(c_id,c_name,c_city,c-street)
Select c_id, c_name,c_cit, c_strett
From Table(s)
Where Conditions
Member Avatar for anubina

hello,
I am not getting which SQL statement should be used to insert multiple data at a same time without writing insert into... again and again.
suppose whenever i want to insert data into 'customer' table then i may write statement as

insert into customer(c_id,c_name,c_city,c-street)
             values('C101','Hayes','Downtown','Main')

But it will create only one row and for next row i have to repeate the same things.

Is there any query which will directly ask me to enter many rows?

Hello,

If you RDBMS is Oracle >=10g, you can use the model query for a UPSERT (optimal solution).

If you RDBMS is Oracle <10g, you have this alternatives:
· PL/SQL procedure with a FOR .. LOOP with execute immediate 'insert into ...'
· You can create a auxiliary table for a cartesian projection (cgyrob)
· You can create a batch script with the preload insert (debasisdas)

Member Avatar for anubina 
insert all into customer (c_id,c_name,c_city,c-street)
   select c_id,c_name,c_city,c-street
     from dual
    model
dimension by (0 AS i)
 measures
(
 cast(NULL AS varchar2(10)) AS c_id,
 cast(NULL AS varchar2(10)) AS c_name,
 cast(NULL AS varchar2(10)) AS c_city,
 cast(NULL AS varchar2(10)) AS c-street
)
    rules UPSERT iterate (10) -- Number of insertions
(
 c_id[iteration_number]='C101',
 c_name[iteration_number]='Hayes',
 c_city[iteration_number]='Downtown',
 c-street[iteration_number]='Main'
);
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.