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

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.

Thanks Trent.  I will look in the training manual.

Jeff

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

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

Jeff, it would look something like this:

Hi Trent,

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

Jeff