0

I am trying to perform the following using a MERGE statement

I have a table that has two columns (TagId is an Identity (PK) and Name as a VARCHAR). I would like to check first if Name exists before I insert it. If it does exist, I would like to get the Identity value. Otherwise, I would insert it and pick up the inserted value.

The beauty of MERGE is it is transactional in nature. so, I won't have to worry about getting an UNIQUE index violation due to timing. Any suggestions? I prefer not to use transactions.

DECLARE 
    @TagId INT,
    @Name VARCHAR(100) = 'TagName'

SELECT TOP(1) 
  @TagId = T.TagId 
FROM dbo.Tag AS T 
WHERE T.Name = @Name 

IF @TagId IS NULL 
BEGIN
INSERT dbo.Tag (Name) VALUES (@Name)

SELECT @TagId = SCOPE_IDENTITY()
END

After trying, this seems to work: it doesn't seem right. The MATCHED clause is required. Otherwise, @Table won't have value. 

DECLARE @Table TABLE 
(
  TagId INT,
  Name VARCHAR(100)
)

DECLARE 
    @TagId INT,
    @Name VARCHAR(100) = 'TdagNamed122'

MERGE dbo.Tag AS Target
USING(SELECT @Name) AS Source (Name)
  ON Source.Name = Target.Name   
WHEN MATCHED THEN 
  UPDATE SET @TagId = Target.TagId 
WHEN NOT MATCHED THEN 
  INSERT (Name) VALUES (Source.Name) 
OUTPUT INSERTED.* INTO @Table 
;

SELECT * FROM @Table
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sam
  • 875
  • 10
  • 22
  • 1
    Why don't you want to use a transaction? – Blorgbeard Aug 22 '14 at 02:22
  • I prefer not to. Transaction would perform a lock on the table and it might be problematic. – Sam Aug 22 '14 at 02:25
  • Why would that be problematic? It's not going to be holding a lock for very long. If you want it atomic, you need a lock. Also, I'm pretty sure `merge` will be locking the table under the covers. – Blorgbeard Aug 22 '14 at 02:34
  • 1
    @Sam: if you *don't* use transactions explicitly, SQL Server will use one **implicitly**, and when updating rows, there will **always** be a lock taken ... – marc_s Aug 22 '14 at 04:49
  • @marc_s, my bad: I was thinking that this statement will prevent that: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED However, after reading about it, I realize that I am wrong. – Sam Aug 22 '14 at 13:48

1 Answers1

0

Yes as per the documentation , it should execute all statements in atomic fashion I didn't face any concurrency issues

But There are some concerns as per the link http://www.mssqltips.com/sqlservertip/3074/use-cauti

radar
  • 13,270
  • 2
  • 25
  • 33