Win a copy of The Journey To Enterprise Agility this week in the Agile and Other Processes forum! And see the welcome thread for 20% off.
  • 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:
  • Jeanne Boyarsky
  • Liutauras Vilda
  • Campbell Ritchie
  • Tim Cooke
  • Bear Bibeault
Sheriffs:
  • Paul Clapham
  • Junilu Lacar
  • Knute Snortum
Saloon Keepers:
  • Ron McLeod
  • Ganesh Patekar
  • Tim Moores
  • Pete Letkeman
  • Stephan van Hulst
Bartenders:
  • Carey Brown
  • Tim Holloway
  • Joe Ess

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
Marshal
Posts: 38502
653
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
Boost this thread!