StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » Creating an Upsert Stored Procedure

Creating an Upsert Stored Procedure

Jeff Pagley — Thu, 06 Nov 2008 09:52:35 GMT

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

RE: Creating an Upsert Stored Procedure

Jeff Pagley — Thu, 06 Nov 2008 09:52:35 GMT

Hi Trent,

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

Jeff

RE: Creating an Upsert Stored Procedure

Trent L. Taylor — Thu, 06 Nov 2008 09:24:41 GMT

Jeff, it would look something like this:

[codesnippet]@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
[/codesnippet]

RE: Creating an Upsert Stored Procedure

Jeff Pagley — Thu, 06 Nov 2008 08:33:22 GMT

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

RE: Creating an Upsert Stored Procedure

Bill Spack — Thu, 06 Nov 2008 08:08:13 GMT

here you go

CREATE PROC [dbo].[Book_upsert]

@Book_pk int,

@Title varchar(30),

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

RE: Creating an Upsert Stored Procedure

Jeff Pagley — Tue, 04 Nov 2008 06:55:15 GMT

Thanks Trent. I will look in the training manual.

Jeff

RE: Creating an Upsert Stored Procedure

Trent L. Taylor — Mon, 03 Nov 2008 09:07:26 GMT

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.