title: MERGE in SQL Server
body: <p>I am trying to perform the following using a <code>MERGE</code> statement </p>
  
  <p>I have a table that has two columns (<code>TagId</code> is an Identity (PK) and <code>Name</code> as a <code>VARCHAR</code>). I would like to check first if <code>Name</code> 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. </p>
  
  <p>The beauty of <code>MERGE</code> is it is transactional in nature. so, I won't have to worry about getting an <code>UNIQUE</code> index violation due to timing. Any suggestions? I prefer not to use transactions.</p>
  
  <pre><code>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 
  </code></pre>
  
  <p>After trying, this seems to work: it doesn't seem right. The <code>MATCHED</code> clause is required. Otherwise, <code>@Table</code> won't have value. </p>
  
  <pre><code>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 
  </code></pre>