updating DB against an existing DB

[ecnarongi]

OK here is my situation I have a DB and I want to update the DB only if the values passed are not present in the DB. The catch is the form will create many records, so for each record I have to check for existence.

This can be very resource intensive, I need help with the routine. My current routine is to grab the info from the database into an array and search each instance of the values passed against the array to check for existence. All help is appreiated.

[fractalvibes]

Could you give us an example of the table in question and the typical data values?

fv

[raf]

you could create a unique index (on one or multiple fiields). An then simple insert the records. If the values for the unique index were already present, the insert will not be performed..

[ecnarongi]

Here is sample input:

field1: James field2: Smith field3: 34 years old
field1: Paul field2: Thomas field3: 29 years old

the table only has these three fields. the form passes all of this information to the asp page. With 2 entries, this is not resource intensive but say I have 500 entries resources would get pushed, if I have to query each field1 against the array of data from the existing DB.

raf, would I be checking against my array each time in your example?? If not please explain, if so I am looking for an easier way.

[glenngv]

So which is your primary key? With those 3 kinds of info, it's not good to make either one of them as primary key.

Ok, granted you already set a primary key...

<%
on error resume next
'do the insert
objConn.Execute("INSERT ...")
if err.number<>0 then
   response.write "Database error occurred.<br>" & err.description
   objConn.close
   set objConn=nothing
   response.end
end if
on error goto 0
%>

[fractalvibes]

By all means, let the database do the work.

Before inserting you could first query the db:

select field1
, field2
, field3
from someDB
where field1 = yourvar1
and field2 = yourvar2
and field3 = yourvar3

Execute the sql - if you get a hit, then you are about to insert a duplicate record, so don't do.

I <* think *> there might be sql syntax to support an insert
where (use a not exists subselect from sql above )

but not completely certain, and if so, is probably DBMS-vendor-specific in support/syntax...

fv

[glenngv]

Originally posted by fractalvibes
By all means, let the database do the work.

That's exactly what I did.
Let the database throw the error and the application catch it.
No need for doing a SELECT query.

[raf]

Originally posted by ecnarongi
raf, would I be checking against my array each time in your example?? If not please explain, if so I am looking for an easier way.

No. You just run the insert and don't need a select or anything.
Just create a multi field index (one index on these three variables, which needs to be set to unique) --> this will prevens records with the same values as one that is already in the table, from being inserted.

This unique index is NOT your primary key. It is just a UNIQUE composit key.

i]Originally posted by fractalvibes[/i]
By all means, let the database do the work.

We do, but in the most efficient way.

i]Originally posted by fractalvibes[/i]I <* think *> there might be sql syntax to support an insert
where (use a not exists subselect from sql above )

but not completely certain, and if so, is probably DBMS-vendor-specific in support/syntax....

Indeed. Like mySQL's REPLACE INTO --> will insert if new, or update if an old record. It works exactly as i described: it inserts the record + if there is a key duplication on a unique index, then it deletes the old record. In this last case, you actualy updated the record.
Oracle also has this kinda feature for incremental refreches. Jet-SQL probably not (not that i know of) and DB2 also doesn't has this feature.

[ecnarongi]

Thanks guys, through asking questions I become a better programmer.