Loading a large file into database

[Alex Loaiza]

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!!

[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 ]

[Jeff Albertson]

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]

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]

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]

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 ...

get first A
get first B
while not A-eof and not B-eof
   if A-Key < B-Key or B-eof
      process A only
      next A
   if A-Key > B-Key or A-eof
      process B only
      next B
   if A-Key == B-Key
      process on both
      next A
      next B
end while

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.