update - ¿Qué es USAR en la sintaxis de SQL Server 2008 MERGE?

sql server merge example insert update

Question

Jacob hizo la pregunta perfecta: dame la sintaxis de MERGE .

Cada respuesta por ahí salta inmediatamente al caso más complicado que se pueda imaginar; oscureciendo la sintaxis con extraña confusión.

Marc dio una respuesta :

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'')
; 

Viendo esta respuesta, estoy tan confundido como Jacob estaba:

No tengo otra tabla

Marc sugirió que someOtherTable es un valor de marcador de posición ficticio, no importa que no tengas esa tabla.

Lo intento, y SQL Server se queja.

Nombre de objeto no válido ''someOtherTable''.

Eso me deja luchando para entender para qué sirve USING en USING foo si no es importante (excepto realmente importante).

¿Qué está utilizando USING cuando usa foo cuando uso la sintaxis de SQL Server 2008 MERGE?

Pregunta extra

¿Cuál es la sintaxis de UPSERT utilizando 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'')

se convierte (código exacto que probé):

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''.

?

Con una tabla de Users que contiene las columnas:

UserGUID uniqueidentifier
Username varchar(50)
FirstName varchar(50)
LastName varchar(50)
AuthenticationMethod varchar(50)

Actualizar:

USING <table_source> 

Donde table_source es:

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> 

Donde joined_table es:

indefinido

Donde pivoted_table es:

indefinido

Donde unpivoted_table es:

indefinido

Answer 1

La tabla de origen puede ser cualquier cosa, como:

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'');

Obviamente, en la selección de fuente anidada puedes hacer muchas más cosas. Seleccione de una vista, una función, una variable de tabla, un CTE incluso.

En cuanto a la pregunta extra, respondiste tu propia pregunta.

A veces, para tablas muy grandes, también uso la sugerencia ROWLOCK en la tabla de destino, para al menos intentar no bloquear toda la tabla en caso de actualizaciones:

MERGE 
   member_topic WITH (ROWLOCK) AS target

En relación con la pregunta de bonificación no funciona, aquí hay una muestra de trabajo. Cambié el nombre de algunos de los objetos, por supuesto.

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);

Answer 2

Siguiendo con la respuesta de Martin Smith, puede aumentar varias filas de valores explícitos a la vez simplemente repitiendo los corchetes, separándolos con una coma, por ejemplo,

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); 

Probé esto en SQL Server 2012. (Habría agregado esto como un comentario pero demasiados caracteres).

Agregué un HOLDLOCK después de ver this , porque si está utilizando MERGE para UPSERT, seguramente el punto se está bloqueando, la sintaxis no está más clara. Vea también el comentario de Marcel sobre ROWLOCK para tablas grandes.

Hubo otro post que encontré más claro que el promedio, también.

Answer 3

Una combinación tiene un origen de tabla y una tabla de destino. Esto introduce la tabla de origen (que no necesita ser una tabla física real, solo un conjunto de resultados).

La gramática está indicada en tu pregunta. Para combinar desde otra tabla o vista use

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

O puedes usar <unpivoted_table> por ejemplo

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);

Para su pregunta de bonificación, puede usar la cláusula VALUES aquí como parte de la opción de 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);