0

I have an Id column set by SEQUENCE Given a varchar I need to INSERT if there is not match, and return an Id.

The Id will be either:

  • The existing Id of the row with the matchine ItemName
  • The new Id generated by the sequence because a row was inserted
  • NULL because the string was passed as NULL

I'm trying to MERGE into the table to insert if not exists, otherwise get the Id. I need it to be threadsafe.

Here is what I have so far but I'm thinking there is a better way:

DECLARE @vValueId INT NULL
DECLARE @inserted AS TABLE (Id INT NOT NULL)

MERGE 
    dbo.foo WITH (HOLDLOCK) AS f 
USING 
    (SELECT @vName AS val WHERE @vName IS NOT NULL) AS new_item
        ON f.ItemName = new_item.val
WHEN MATCHED THEN
    UPDATE SET @vValueId = f.Id
WHEN NOT MATCHED BY TARGET THEN
    INSERT
      (ItemName)
    VALUES
      (@vName)
OUTPUT inserted.Id AS Id INTO @inserted;

SELECT @vValueId = ISNULL(s.Id, @vValueId) FROM @inserted AS s
Matthew
  • 10,244
  • 5
  • 49
  • 104
  • I don't really understand the need of using meagre in this situation. If there is a match you don't update the table, so why not just use a simple insert instead? – Zohar Peled Dec 12 '16 at 14:09
  • @ZoharPeled because this is a threadsafe insert and retrieves the values without a second query. How would you do it instead? Remember that I need the Id value. – Matthew Dec 13 '16 at 17:35

0 Answers0