SELECT before INSERT or UPDATE 1

[Niv3k]

Is there any better way to check for a record's existence before insert/updates other than SELECT?

For example, I poll many pieces of hardware, and put the results in a database. I want to see if equipment ABC is listed with IP Address 1.2.3.4. If it is already there, I want to update the desciption fields, otherwise, I need to add it.

Basically, on the client side (in this case the poller) I run:
SELECT * FROM Hardware WHERE Device='ABC' AND IP='1.2.3.4'

If I don't return a record, then I INSERT the appropriate data. If I do return a record, I UPDATE that record.

If I still need to do this, should I convert the whole thing to a stored procedure on the server? I want the process to be as efficient as possible, as I will be updating thousands of records every time I run the poller.

Thanks for all input.

Kevin

PS - I run SQL 2000, and don't have many end users (<100), front end is ASP.

 

[dbmsguy]

from the sounds of it you are just doing existance checking...

so...

1. i would do it from a stored proc. would be more efficient and keeps the network traffic down.
2. you don't really have to select anything, just get a true or false, so don't do a select *, something like:

if exists (select 'x' from Hardware WHERE Device='ABC' AND IP='1.2.3.4')
begin
select @test='true'
end

you would then return the test restult and either do the update of not.....

something like that would work. Paul

http://www.dbmsguy.com

 

[Niv3k]

Alright! That's pretty smooth... I just ran a quick test, and I like it. Thanks.

Kevin

 

[dbmsguy]

from your description of the process, using this method you could probably do the whole thing in a stored proc...

instead of returning a true or false, decide in the sp what to do and as long as all of the data is passed down for the insert, you could implement that decision in the stored proc. Client would never be involved except to send down the sp exec, just send back the results. Paul

http://www.dbmsguy.com

 

[Niv3k]

Actually, that's exactly what I was planning on doing, from the first post of yours. I like the idea of keeping network traffic low. And the less calculation the ASP has to do, well, that's all the better for speed anyway.

 

[bluewolf]

In my opinion, you can write triggers for INSERT, UPDATE. If have an error, you can return a value to process problem. BW.

 

[Niv3k]

My understanding of triggers doesn't allow that to make sense. What would I trigger on? a SELECT? If I did that then wouldn't a trigger fire every time someone looked at the row or table, even if the have read-only intentions?