StrataFrame Forum

Creating an Upsert Stored Procedure

http://forum.strataframe.net/Topic20512.aspx

By Jeff Pagley - 11/3/2008

Hi SF Team,

In the training course and manual was a stored procedure that was referred to called an Upsert.  Could you please give me a T-SQL example of this sproc for updating/inserting a group records at one time.

Thanks,

Jeff

By Trent L. Taylor - 11/3/2008

UPSERTs are designed more for creating or updating a single record.  Now you may have a sproc that gets recusrivly called and updates a group of records, but generally UPSERts are designed for single record updates.

You might look at the training manual as I believe there is a sample in there from Ben's session.

By Jeff Pagley - 11/4/2008

Thanks Trent.  I will look in the training manual.

Jeff

By Bill Spack - 11/6/2008

here you go

CREATE PROC [dbo].[Book_upsert]

    @Book_pk int,

    @Title varchar(30),

AS

SET NOCOUNT ON

IF @Book_pk = 0

  BEGIN

      INSERT INTO Book ([Title]) VALUES (@Title)

      SELECT SCOPE_IDENTITY() As InsertedID

  END

ELSE

  BEGIN

      UPDATE Book SET [Title] = @Title WHERE [Book_pk] = @Book_pk

  END

SET NOCOUNT OFF
By Jeff Pagley - 11/6/2008

Thanks for the sproc.  However, what I was looking for is a sproc which first tries to update the record, and if the affected record count is 0, then the sproc assumes that the record does not exist to update, so it inserts the record.  I think I know how I am going to create it, but I haven't done it yet.

Jeff

By Trent L. Taylor - 11/6/2008

Jeff, it would look something like this:

@userPk INT,
@eventLocationPk BIGINT
AS
BEGIN

-- Turn off row counting
SET NOCOUNT ON;

-- Declare variables
DECLARE @recCount INT;

-- Perform a scalar query to determine if the
-- record already exists
SELECT
 @recCount = COUNT(* )
FROM UserLocations
WHERE ul_us_pk = @userPk

-- Now determine how to update the record
IF @recCount > 0
 BEGIN
  -- Update the existing record
  UPDATE UserLocations
   SET ul_vel_pk = @eventLocationPk
  WHERE ul_us_pk = @userPk
 END
ELSE
 BEGIN
  -- Create the location record
  INSERT INTO UserLocations (
   ul_us_pk,
   ul_vel_pk)
   VALUES (    
   @userPk,
   @eventLocationPk)
 END
 
END 
 
By Jeff Pagley - 11/6/2008

Hi Trent,

Thank you for taking the time to send me the example.  It was what I was looking for.

Jeff