Insert a Row Only if a Row does not Exist

Question

I am building a hit counter. I have an article directory and tracking unique visitors. When a visitor comes i insert the article id and their IP address in the database. First I check to see if the ip exists for the article id, if the ip does not exist I make the insert. This is two queries -- is there a way to make this one query

Also, I am not using stored procedures I am using regular inline sql

Answer 1

try this (it's a real kludge, but it should work...):

Insert TableName ([column list])
Select Distinct @PK, @valueA, @ValueB, etc. -- list all values to be inserted
From TableName
Where Not Exists 
    (Select * From TableName
     Where PK == @PK)

Answer 2

Not with SQL Server. With T-SQL you have to check for the existence of a row, then use either INSERT or UPDATE as appropriate.

Another option is to try UPDATE first, and then examine the row count to see if there was a record updated. If not, then INSERT. Given a 50/50 chance of a row being there, you have executed a single query 50% of the time.

MySQL has a extension called REPLACE that has the capability that you seek.