Win a copy of Microservices in Action this week in the Web Services forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Devaka Cooray
  • Liutauras Vilda
  • Jeanne Boyarsky
Sheriffs:
  • Knute Snortum
  • Junilu Lacar
  • paul wheaton
Saloon Keepers:
  • Ganesh Patekar
  • Frits Walraven
  • Tim Moores
  • Ron McLeod
  • Carey Brown
Bartenders:
  • Stephan van Hulst
  • salvin francis
  • Tim Holloway

Multi-row insert  RSS feed

 
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.
 
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?
 
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes. Insert:

Update:
 
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
 
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.
 
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.
 
author & internet detective
Posts: 38925
686
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.
 
Consider Paul's rocket mass heater.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!