Multi-row insert

[anuja Edathu]

Hi all,
Is there any way to insert or update multi-row in to a table.
Can any one give me some suggestions or relevant links
Thanks & Regards,
Anuja K.

[Sripathi Krishnamurthy]

what do you mean by inseting multi-row? Inserting 2 or more rows at the same time?

[Paul Sturrock]

Yes. Insert:

select ... into ... from ...

Update:

update table set field = [value] where ...

[Adeel Ansari]

And if you are talking in java then you can use executeBatch() for batch insert.
[ October 05, 2005: Message edited by: Adeel Ansari ]

[anuja Edathu]

Hi
Thanks all but still i couldn't figure out any thing.First i'll tell you my problem.

I am having a table say xx with three fields say x is primary key,
i want to isert 2 or more row into the table ,if that rows are found( value for primary key is not in the table) i have to insert these rows, else i need to update these rows.

I thought of writting a procedure which'll update if select statement throws exception else insert the rows.
But then i have to call this procedure as many times as the number of rows.

Is there any way i can reduce the database access.

Im new to oracle .
can any one help?
Regards,
Anuja K

[Rajasekar Elango]

Although this is not related to EJB, this thread may give you some idea..

Regards,
Raja

[Paul Sturrock]

Ok, I think I understand what you want - you are trying to add records to a table when the records may already be there? If so just issue a select query and update/insert based on what it returns.

[Maximilian Xavier Stocker]

As mentioned you can do a SELECT to see if your row exists first but you can also see if your database supports REPLACE syntax. A REPLACE query inserts values if the row does not exist or updates values if the row does exist.

[anuja Edathu]

Hi,
I am using Oracle DataBase ,I don't know a way to replcae a row in oracle.
Is there any way to do this, Or can we write a trigger on the table
before update if the row doesn't exist to perform insert in to the table?
Which will be a better approach, trigger or a stored procedure ?

Thanks & Regards
Anuja K.

[Jeanne Boyarsky]

Anuja,
It depends on who has access to the table. A trigger protects you against updates to the table that don't go through the stored proc.

[anuja Edathu]

Hi All,

I decided to use the stored procedure .Thank you All,
Regards,
Anuja K.