aspose file tools*
The moose likes JDBC and the fly likes Multi-row insert Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Multi-row insert" Watch "Multi-row insert" New topic
Author

Multi-row insert

anuja Edathu
Ranch Hand

Joined: Sep 09, 2005
Posts: 84
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
Ranch Hand

Joined: Mar 07, 2005
Posts: 232
what do you mean by inseting multi-row? Inserting 2 or more rows at the same time?
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Yes. Insert:

Update:


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
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
Ranch Hand

Joined: Sep 09, 2005
Posts: 84
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
Ranch Hand

Joined: Sep 13, 2004
Posts: 105
Although this is not related to EJB, this thread may give you some idea..

Regards,
Raja


SCJP 1.4
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

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
Ranch Hand

Joined: Sep 20, 2005
Posts: 381
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
Ranch Hand

Joined: Sep 09, 2005
Posts: 84
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30537
    
150

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
anuja Edathu
Ranch Hand

Joined: Sep 09, 2005
Posts: 84
Hi All,

I decided to use the stored procedure .Thank you All,
Regards,
Anuja K.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Multi-row insert