multiple data insertion

[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?

[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.

[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

[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)

[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'
);