We often encounter the "If not exists, insert" situation. Dan Guzman's blog has an excellent investigation of how to make this process threadsafe.
I have a basic table that simply catalogs a string to an integer from a SEQUENCE. In a stored procedure I need to either get the integer key for the value if it exists, or INSERT it and then get the resulting value. There is a uniqueness constraint on the dbo.NameLookup.ItemName column so data integrity isn't at risk but I don't want to encounter the exceptions.
It's not an IDENTITY so I can't get SCOPE_IDENTITY and the value could be NULL in certain cases.
In my situation I only have to deal with INSERT safety on the table so I'm trying to decide if it's better practice to use MERGE like this:
SET NOCOUNT, XACT_ABORT ON;
DECLARE @vValueId INT
DECLARE @inserted AS TABLE (Id INT NOT NULL)
MERGE
dbo.NameLookup WITH (HOLDLOCK) AS f
USING
(SELECT @vName AS val WHERE @vName IS NOT NULL AND LEN(@vName) > 0) 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 = s.Id FROM @inserted AS s
I could do this witout using MERGE with just a conditional INSERT followed by a SELECT
I think that this second approach is clearer to the reader, but I'm not convinced it's "better" practice
SET NOCOUNT, XACT_ABORT ON;
INSERT INTO
dbo.NameLookup (ItemName)
SELECT
@vName
WHERE
NOT EXISTS (SELECT * FROM dbo.NameLookup AS t WHERE @vName IS NOT NULL AND LEN(@vName) > 0 AND t.ItemName = @vName)
DECLARE @vValueId int;
SELECT @vValueId = i.Id FROM dbo.NameLookup AS i WHERE i.ItemName = @vName
Or perhaps there is another better way that I haven't considered
I did search and reference other questions. This one: http://stackoverflow.com/questions/5288283/sql-server-insert-if-not-exists-best-practicehttps://stackoverflow.com/questions/5288283/sql-server-insert-if-not-exists-best-practice is the most appropriate I could find but doesn't seem very applicable to my use case. Other questions to the IF NOT EXISTS() THEN approach which I don't think is acceptable.
