• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Loading a large file into database

 
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Anyone knows which is the best approach to load a large file (about 50000 rows) into database through a web application. The process must check if the record exists and update it, otherwise insert it!

Thanks for your help!!
 
(instanceof Sidekick)
Posts: 8791
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Just curious how a web application gets a 50,000 line file. Does the user upload it?

I'd look first to see if the database comes with any batch import utilities or if you have third party utilities available.

Failing that, if the file is sorted on a "primary key" field, I'd query all the keys from the database and use the old "master update" algorithm to decide whether to insert or update each line from the file.

If the file is not sorted (or sortable) you may be stuck with trying to query each key from the file to see if it already exists. Or try insert and see if it fails on duplicate key.
[ December 12, 2005: Message edited by: Stan James ]
 
Ranch Hand
Posts: 1780
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
If your database supports it there may be a command that combines
insert and update. For example in Oracle 9i or later, there is a merge command:

MERGE <hint> INTO <table_name>
USING <table_view_or_query>
ON (<condition>)
WHEN MATCHED THEN <update_clause>
WHEN NOT MATCHED THEN <insert_clause>;
[ December 12, 2005: Message edited by: Jeff Albrechtsen ]
 
Alex Loaiza
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Stan James:
Just curious how a web application gets a 50,000 line file. Does the user upload it?

I'd look first to see if the database comes with any batch import utilities or if you have third party utilities available.

Failing that, if the file is sorted on a "primary key" field, I'd query all the keys from the database and use the old "master update" algorithm to decide whether to insert or update each line from the file.

If the file is not sorted (or sortable) you may be stuck with trying to query each key from the file to see if it already exists. Or try insert and see if it fails on duplicate key.

[ December 12, 2005: Message edited by: Stan James ]




Could you please be more specific with the "master update" algorithm?? Thanks!!
 
Alex Loaiza
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Jeff Albrechtsen:
If your database supports it there may be a command that combines
insert and update. For example in Oracle 9i or later, there is a merge command:

MERGE <hint> INTO <table_name>
USING <table_view_or_query>
ON (<condition>
WHEN MATCHED THEN <update_clause>
WHEN NOT MATCHED THEN <insert_clause>;

[ December 12, 2005: Message edited by: Jeff Albrechtsen ]



Well, thanks for the hint, but I need the solution to be Database independent....
 
Stan James
(instanceof Sidekick)
Posts: 8791
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Master-Update dates from the days of punch cards. I post it here fairly often (!) which may illustrate how when your favorite tool is a hammer all problems look like nails. Call our two data sources A and B ...

You might think of A as your database master and B as your update file. You'd only have to select the key from the database, not the whole record, to figure out whether each file record is already on database. It could be a relatively large query, but only one and outside any of your update transactions.
 
ice is for people that are not already cool. Chill with this tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic