• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Multi-row insert

 
anuja Edathu
Ranch Hand
Posts: 84
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 232
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
what do you mean by inseting multi-row? Inserting 2 or more rows at the same time?
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes. Insert:

Update:
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 84
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 105
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Although this is not related to EJB, this thread may give you some idea..

Regards,
Raja
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 381
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 84
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 34669
366
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 84
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,

I decided to use the stored procedure .Thank you All,
Regards,
Anuja K.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic