Что такое ИСПОЛЬЗОВАНИЕ в синтаксисе MERGE SQL Server 2008?

Якоб задал идеальный вопрос: дать мне синтаксис MERGE.

Каждый ответ там сразу переходит в самый сложный случай, о котором они могут подумать; скрывая синтаксис с посторонней путаницей.

Марк дал ответ:

MERGE 
   member_topic AS target
USING 
   someOtherTable AS source
ON 
   target.mt_member = source.mt_member 
   AND source.mt_member = 0 
   AND source.mt_topic = 110
WHEN MATCHED THEN 
   UPDATE SET mt_notes = 'test'
WHEN NOT MATCHED THEN 
   INSERT (mt_member, mt_topic, mt_notes) VALUES (0, 110, 'test')
;

Глядя на этот ответ, я так же смущен, как Джейкоб:

У меня нет someOtherTable

Марк предположил, что someOtherTable является фиктивным значением-заполнителем - не имеет значения, что у вас нет этой таблицы.

Я пытаюсь это сделать, и SQL Server жалуется

Недопустимое имя объекта 'someOtherTable'.

Это оставляет мне попытку понять, что означает USING in USING foo, если это не важно (кроме действительно важных).

Что используется USING при использовании foo при использовании синтаксиса MERGE SQL Server 2008?

Бонусный вопрос

Что такое синтаксис UPSERT с помощью MERGE:

IF (rowExists)
   UPDATE Users SET Firstname='Ian', LastName='Boyd' WHERE Username='iboyd'
ELSE
   INSERT INTO Users (UserGUID, Username, FirstName, LastName, AuthenticationMethod)
   VALUES ('{77410DC5-7A3E-4F1A-82C6-8EFB3068DE66}', 'iboyd', 'Ian', 'Boyd', 'Windows')

становится (точный код, который я пробовал):

begin transaction

    MERGE 
       Users
    USING 
       foo
    ON  
       Users.UserName = foo.UserName
    WHEN MATCHED THEN
        UPDATE SET Firstname = foo.FirstName, Lastname = foo.LastName
    WHEN NOT MATCHED THEN
        INSERT (UserGUID, Username, FirstName, LastName, AuthenticationMethod)
        VALUES ('{77410DC5-7A3E-4F1A-82C6-8EFB3068DE66}', 'iboyd', 'Ian', 'Boyd', 'Windows')
    ; --A MERGE statement must be terminated by a semi-colon (;).

rollback

Msg 208, Level 16, State 1, Line 3
Invalid object name 'foo'.

С таблицей Users, содержащей столбцы:
UserGUID uniqueidentifier
Username varchar(50)
FirstName varchar(50)
LastName varchar(50)
AuthenticationMethod varchar(50)

Update:

USING <table_source>

Где table_source:

table_or_view_name [ [ AS ] table_alias ] [ <tablesample_clause> ] 
    [ WITH ( table_hint [ [ , ]...n ] ) ] 
| rowset_function [ [ AS ] table_alias ] 
    [ ( bulk_column_alias [ ,...n ] ) ] 
| user_defined_function [ [ AS ] table_alias ]
| OPENXML <openxml_clause> 
| derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ] 
| <joined_table> 
| <pivoted_table> 
| <unpivoted_table>

Где joined_table:

undefined

Где pivoted_table:

undefined

Где unpivoted_table:

undefined

Ответы

Ответ 1

Слияние имеет источник таблицы и целевую таблицу. Это вводит исходную таблицу (которая не обязательно должна быть реальной физической таблицей, просто набором результатов).

В вашем вопросе указывается грамматика. Чтобы слить из другой таблицы или просмотра, используйте

MERGE 
   Users
USING SomeOtherTableName AS foo /*Alias is optional*/
ON /* ... */

Или вы можете использовать <unpivoted_table>, например

MERGE 
   Users
USING master..spt_values
UNPIVOT (X FOR Y IN ([high],[low])) AS foo 
ON  
   Users.Username = foo.Y 
WHEN MATCHED THEN
    UPDATE SET FirstName = foo.Y
WHEN NOT MATCHED THEN
    INSERT (UserGUID, Username, FirstName, LastName, AuthenticationMethod)
    VALUES (foo.Y, foo.Y, foo.Y, foo.Y, foo.Y);

Для вашего бонусного вопроса вы можете использовать предложение VALUES здесь как часть опции derived_table.

MERGE Users
USING (VALUES ('{77410DC5-7A3E-4F1A-82C6-8EFB3068DE66}',
      'iboyd',
      'Ian',
      'Boyd',
      'Windows')) AS foo(UserGUID, Username, FirstName, LastName, AuthenticationMethod)
ON Users.UserName = foo.UserName
WHEN MATCHED THEN
  UPDATE SET Firstname = foo.FirstName,
             Lastname = foo.LastName
WHEN NOT MATCHED THEN
  INSERT (UserGUID,
          Username,
          FirstName,
          LastName,
          AuthenticationMethod)
  VALUES (UserGUID,
          Username,
          FirstName,
          LastName,
          AuthenticationMethod);

Ответ 2

Таблица источников может быть любой, например:

MERGE 
   member_topic AS target
USING 
   (SELECT @Variable1, @Variable2, @Variable3) AS source(Col1, Col2, Col3)
ON 
   target.mt_member = source.Col1 
   AND source.Col1 = 0 
   AND source.Col2 = 110
WHEN MATCHED THEN 
   UPDATE SET mt_notes = 'test'
WHEN NOT MATCHED THEN 
   INSERT (mt_member, mt_topic, mt_notes) VALUES (0, 110, 'test');

Очевидно, что во вложенном исходном элементе вы можете делать гораздо больше. Выберите из представления, функции, переменной таблицы, даже CTE.

Что касается вопроса о бонусе, вы ответили на свой вопрос.

Иногда для очень больших таблиц я также использую подсказку ROWLOCK в целевой таблице, чтобы хотя бы попытаться не заблокировать всю таблицу в случае обновлений:

MERGE 
   member_topic WITH (ROWLOCK) AS target

Связанный с бонусом вопрос не работает, вот рабочий образец. Конечно, я переименовал некоторые объекты.

DECLARE @Variable1 AS INT;
SET @Variable1 = 1234;

MERGE dbo.Table1 WITH(ROWLOCK) target
USING(SELECT @Variable1) source(Key)
ON target.[Key] = source.[Key]
WHEN MATCHED THEN
    UPDATE SET
    Col1 = @SomeVar1,
    Col2 = @SomeVar2
WHEN NOT MATCHED THEN
INSERT 
        ([Key]
        ,[Col1]
        ,[Col2])
    VALUES
        (@Variable1
        ,@SomeVar1
        ,@SomeVar2);

Ответ 3

Следуя ответу Мартина Смита, вы можете сразу несколько строк значений сразу добавить несколько строк значений, просто повторяя скобки, разделяя их запятой, например

MERGE Users WITH (HOLDLOCK)
USING (VALUES ('{77410DC5-7A3E-4F1A-82C6-8EFB3068DE66}',
      'iboyd',
      'Ian',
      'Boyd',
      'Windows'),
      ('{00000DC5-7A3E-4F1A-82C6-8EF452D2DE66}',
      'jsmith',
      'John',
      'Smith',
      'ActiveDirectory')) AS foo(UserGUID, Username, FirstName, LastName, AuthenticationMethod)
ON Users.UserName = foo.UserName
WHEN MATCHED THEN
  UPDATE SET Firstname = foo.FirstName,
             Lastname = foo.LastName
WHEN NOT MATCHED THEN
  INSERT (UserGUID,
          Username,
          FirstName,
          LastName,
          AuthenticationMethod)
  VALUES (UserGUID,
          Username,
          FirstName,
          LastName,
          AuthenticationMethod);

Протестировано на SQL Server 2012. (Добавил бы это как комментарий, но слишком много символов.)

Я добавил HOLDLOCK, увидев this, потому что, если вы используете MERGE для UPSERT, безусловно, точка блокируется, синтаксис, безусловно, нет яснее. См. Также комментарий Марселя о ROWLOCK для больших таблиц.

Было другое сообщение Я нашел более ясным, чем средний, тоже.