sqlserver how to do a insert, but if it exists update instead maneuver?

Read a few things online and here but am now confused. What I want to do is a simple insert... but if the record already exists I want to update it instead, how is this best achieved in ms sqlserver?

Here is a simple example of the insert:

INSERT INTO Advertiser开发者_开发知识库CategoryJoin
                  (AdvertiserID, CategoryID)
VALUES     (502910, 2)

I was trying to use ON DUPLICATE KEY but sqlserver doesn't have an equivalent, which was annoying, so what is the best way of doing this?

You can always use the IF..ELSE conditions.

IF (SELECT COUNT(*) FROM AdvertiserCategoryJoin WITH(NOLOCK) WHERE AdvertiserID = 502910) = 1 BEGIN
UPDATE AdvertiserCategoryJoin
SET CategoryID = 2
WHERE AdvertiserID = 502910
END ELSE BEGIN
INSERT INTO AdvertiserCategoryJoin
                  (AdvertiserID, CategoryID)
VALUES     (502910, 2)
END

But there are other ways to do this too, using EXCEPT or EXISTS. It is really up to you and what you find easier to write/maintain, and as always if you are using SQL 2008 and are up for a challenge, you can use the MERGE command, which does exactly what you want.

Assuming you have SQL2008 version, there´s a very good statement for that. MERGE

MERGE AdvertiserCategoryJoin AS target
USING (SELECT @AdvertiserID, @CategoryID) AS source (AdvertiserID, CategoryID)
ON (target.AdvertiserID = source.AdvertiserID)
WHEN MATCHED THEN 
    UPDATE SET CategoryID = source.CategoryID
WHEN NOT MATCHED THEN   
    INSERT (AdvertiserID, CategoryID)
    VALUES (source.AdvertiserID, source.CategoryID)
    OUTPUT deleted.*, $action, inserted.* INTO #MyTempTable;