Creating an Upsert Stored Procedure

StrataFrame Forum

Back

Creating an Upsert Stored Procedure

Author	Message
Jeff Pagley	Jeff Pagley posted 16 Years Ago Post Details Share Post
Group: StrataFrame Users Posts: 223, Visits: 893	Hi Trent, Thank you for taking the time to send me the example. It was what I was looking for. Jeff
	Reply Like 0
Trent Taylor	Trent Taylor posted 16 Years Ago Post Details Share Post
Group: StrataFrame Developers Posts: 6.6K, Visits: 6.9K	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
	Reply Like 0
Jeff Pagley	Jeff Pagley posted 16 Years Ago Post Details Share Post
Group: StrataFrame Users Posts: 223, Visits: 893	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
	Reply Like 0
Bill Spack	Bill Spack posted 16 Years Ago Post Details Share Post
Group: Forum Members Posts: 4, Visits: 17	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
	Reply Like 0
Jeff Pagley	Jeff Pagley posted 16 Years Ago Post Details Share Post
Group: StrataFrame Users Posts: 223, Visits: 893	Thanks Trent. I will look in the training manual. Jeff
	Reply Like 0
Trent Taylor	Trent Taylor posted 16 Years Ago Post Details Share Post
Group: StrataFrame Developers Posts: 6.6K, Visits: 6.9K	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.
	Reply Like 0
Jeff Pagley	Jeff Pagley posted 16 Years Ago Topic Details Share Topic
Group: StrataFrame Users Posts: 223, Visits: 893	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
	Reply Like 0