Update Row or Insert if Doesn’t Exist

When adding an entry to a database it would be nice to combine the addition with an update.

For example adding a new entry if it already exists then that entry can be updated.

This means putting more of the sequence within the database stored procedure. But it will save on a database query, with possible action – the addition, and a result confirming that the addition occurred, or a value to denote that it already exists.

If the entry already exists then there is a further call to the database to update that entry.

Dependant upon the circumstance it may be better to combine this sequence all within a single stored procedure call to the database.

There is the option to check if a particular row value exists. If it does then update or add a new entry.

Here’s the initial setup of the stored procedure, with the passed in parameters:

CREATE PROCEDURE AddUpdateProduct
  @ProductId   INT
  @Name        NVARCHAR(150)
  @Details     NVARCHAR(max)

Lets begin with a classic update

UPDATE
  Product
SET
  Name = @Name,
  Details = @Details
WHERE
  ProductId = @ProductId

Similarly an insert would look like:

INSERT INTO
  Product
  (
    Name,
    Details
  )
  VALUES
  (
    @Name,
    @Details
  )
SELECT SCOPE_IDENTITY()

I’ve added SELECT SCOPE_IDENTITY(), to retrieve the ID of the added entry.

In our example we wish to combine the two together. We’ll start by trying to update the entry.

A test on the rowcount will tell us whether we were successful. If no rows were affected then a new row is added.

Here’s the  example with the combined code:

CREATE PROCEDURE AddUpdateProduct
  @ProductId   INT
  @Name        NVARCHAR(150)
  @Details     NVARCHAR(max)
  UPDATE
    Product
  SET
    Name = @Name,
    Details = @Details
  WHERE
    ProductId=@ProductId
  IF @@ROWCOUNT =0
    BEGIN
      INSERT INTO Product
      (
        Name,
        Details
      )
      VALUES
      (
        @Name,
        @Details
      )
      SELECT SCOPE_IDENTITY()
    END

A short piece of SQL to update an existing row in a database table, or if the row doesn’t exist then to add a new entry.

I like this idea. By putting the whole action in the SQL we are saving going backwards and forwards between the database server and our website software.