0

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
5
Views
8 Years
Discussion Span
Last Post by anubina
0

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.

0

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
0

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)

0
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'
);
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.