UPDATE vs. INSERT

[emozley]

I have got every hostname stored in an array. I now want to add each hostname to my table but if the hostname is already in the table I want to update it instead.

Will I have to take the first element in load all the records in the table in into a recordset then loop through the entire recordset and check it against the first element in the array then do my if...then...else statement and then repeat the whole loop for the 2nd element in the array and repeat until the end of the array or is there a more efficient way?

Thanks!

 

[Sheco]

if your table of hosts gets very large then you want to use the database to do as much of the work as possible... for larger number of items the database is more efficient at discovering if it already exists than looping through a list in ASP.

Actually even setting the recordset filter would probably be faster than looping... because it would be compiled looping...hehe

anyway, one way to make better use of the database is to do something like:
Set rs = cn.Execute("SELECT Count(*) FROM tAllHosts WHERE HostName = 'blah'")
if rs(0) > 0 then
'do update
else
'do insert
end if


Probably the best way would be to make yourself a little stored procedure where you just pass all the stuff and then the database decides if it will do an insert or update.

 

[Genimuse]

Or possibly:

Set rs = cn.Execute("SELECT idField FROM tAllHosts WHERE HostName = 'blah'")
if not rs.eof then
  'do update
else
  'do insert
end if

as I think that'll be faster than an aggregate function.

 

[Sheco]

I agree Genimuse, it will be faster except for very large number of records in tAllHosts.