Using Case statement to either Insert into a table

Question

Possible Duplicate:
Solutions for INSERT OR UPDATE on SQL Server
Only inserting a row if it's not already there

My title pretty much explains what I'm trying to do, but I'll go into a little more detail. I'm creating a stored procedure when called it first checks to see if the row already exists (by comparing against two parameters) and if it does, it will update a specific column in the row and if the row doesn't exist already it will insert a new row into the table.

BEGIN
SELECT 
(
CASE WHEN [Site] = @site and Plant = @plant
then 
UPDATE [Status]
FROM Server_Status
WHERE [Site] = @site
ELSE
Insert into Server_Status(Name, [Path], [Site], Plant, [Status])
Values (@name, @path, @site, @plant, @status)
end
)
FROM Server_Status 
END

Is what I have so far, but doesn't work (obviously). Does anyone with more SQL knowledge than I have any suggestions?

-J

Answer 1

You should first check for the existence of the row using the IF EXISTS statement as follows:

IF EXISTS (SELECT * FROM Server_Status WHERE Site = @Site) 
BEGIN
    -- UPDATE statement.
END
ELSE
    -- INSERT statement.
END

Answer 2

You can do:

IF EXISTS(SELECT * FROM MyTable WHERE...)
 --value exists perform update
  BEGIN
    UPDATE...
  END
ELSE
  --value doesnt exist perform insert
  BEGIN
    INSERT ...
  END

Answer 3

You might want to take ta look at the MERGE (Transact-SQL) statement.

Performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.