WIKI de apuntes de Billy Rondón

 
CREATE TABLE #tmp
	(id INT IDENTITY(1,1) PRIMARY KEY,
	 someval INT NOT NULL,
	 someguid uniqueidentifier NOT NULL DEFAULT newid()
	);
INSERT #tmp( someval )
OUTPUT inserted.id, inserted.someval, inserted.someguid
VALUES (4),(5),(6)

http://blogs.lobsterpot.com.au/2012/06/12/merge-gives-better-output-options/

https://stackoverflow.com/questions/32125738/update-records-based-on-inserted-ids-and-another-non-source-column-in-sql

https://stackoverflow.com/questions/10999396/how-do-i-use-an-insert-statements-output-clause-to-get-the-identity-value

http://dblearner.com/clasula-output-en-sql-server/

https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver15

--Sincronizar la tabla TARGET con
--los datos actuales de la tabla SOURCE
MERGE Usuarios AS TARGET
USING UsuariosActual AS SOURCE 
   ON (TARGET.Codigo = SOURCE.Codigo) 
--Cuandos los registros concuerdan con por la llave
--se actualizan los registros si tienen alguna variación
 WHEN MATCHED AND TARGET.Nombre <> SOURCE.Nombre 
   OR TARGET.Puntos <> SOURCE.Puntos THEN 
   UPDATE SET TARGET.Nombre = SOURCE.Nombre, 
              TARGET.Puntos = SOURCE.Puntos 
--Cuando los registros no concuerdan por la llave
--indica que es un dato nuevo, se inserta el registro
--en la tabla TARGET proveniente de la tabla SOURCE
 WHEN NOT MATCHED BY TARGET THEN 
   INSERT (Codigo, Nombre, Puntos) 
   VALUES (SOURCE.Codigo, SOURCE.Nombre, SOURCE.Puntos)
--Cuando el registro existe en TARGET y no existe en SOURCE
--se borra el registro en TARGET
 WHEN NOT MATCHED BY SOURCE THEN 
   DELETE
 
--Seccion opcional e informativa
--$action indica el tipo de accion
--en OUTPUT retorna cualquiera de las 3 acciones 
--'INSERT', 'UPDATE', or 'DELETE', 
OUTPUT $action, 
DELETED.Codigo AS TargetCodigo, 
DELETED.Nombre AS TargetNombre, 
DELETED.Puntos AS TargetPuntos, 
INSERTED.Codigo AS SourceCodigo, 
INSERTED.Nombre AS SourceNombre, 
INSERTED.Puntos AS SourcePuntos; 
SELECT @@ROWCOUNT;
GO
 
SELECT * FROM Usuarios
SELECT * FROM UsuariosActual

• https://www.dotnetcr.com/merge-en-sql-server-insert-delete-update/

MERGE INTO Table3 USING
(
    SELECT NULL AS col2, 
           110 AS col3, 
           Table1.ID AS col4, 
           Table2.Column2 AS col5,
           Table2.Id AS col6
    FROM Table1
    JOIN Table1Table2Link ON Table1.ID=Table1Table2Link.Column1
    JOIN Table2 ON Table1Table2Link.Column2=Table2.ID
) AS s ON 1 = 0 -- Always not matched
WHEN NOT MATCHED THEN
INSERT (Column2, Column3, Column4, Column5)
VALUES (s.col2, s.col3, s.col4, s.col5)
OUTPUT Inserted.ID, s.col6
INTO @MyTableVar (insertedId, Table2Id); 

• https://stackoverflow.com/questions/41184310/insert-into-merge-select-sql-server

Buen uso de los ouput:

CREATE PROCEDURE [dbo].[TestMerge]
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
 
    MERGE TABLE_TARGET AS T
    USING TABLE_SOURCE AS S
    ON (T.Code = S.Code) 
    WHEN MATCHED AND T.IsDeleted = 0x0
        THEN UPDATE SET ....
    WHEN NOT MATCHED BY TARGET 
        THEN INSERT ....
    OUTPUT inserted.SqlId, inserted.IncId
    INTO sync_table
    OUTPUT $action AS MergeAction, inserted.Name, inserted.Code;
END

https://stackoverflow.com/questions/17165870/multiple-output-clauses-in-merge-insert-delete-sql-commands

CREATE TABLE tvp (oid INT, Title VARCHAR(100));
INSERT INTO tvp VALUES (0,'ProjectX'),(1,'ProjectY'),(2,'ProjectZ');
 
CREATE TABLE #tmp (
    Id INT NOT NULL IDENTITY(1,1)
  , oid INT
  , ProjectId INT
  , Title VARCHAR(100)
);
CREATE TABLE Project (
    ProjectId INT NOT NULL IDENTITY(33,1)
  , Title VARCHAR(100)
);
 
MERGE INTO Project AS Target
USING tvp AS SOURCE
  ON 1 = 0
WHEN NOT matched THEN
INSERT (Title)
  VALUES (SOURCE.Title)
output SOURCE.oid, Inserted.ProjectId, Inserted.Title
INTO #tmp;
 
SELECT * FROM #tmp;

https://stackoverflow.com/questions/43661917/tsql-merge-2-ids-into-temp-table

https://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at-the-same-time/#:~:text=Beginning%20with%20SQL%20Server%202008,operations%20in%20a%20single%20statement.&text=The%20MERGE%20statement%20basically%20merges,in%20the%20target%20or%20not.'

CREATE TABLE tvp (oid INT, Title VARCHAR(100));
INSERT INTO tvp VALUES (0,'ProjectX'),(1,'ProjectY'),(2,'ProjectZ');
 
CREATE TABLE #tmp (
    Id INT NOT NULL IDENTITY(1,1)
  , oid INT
  , ProjectId INT
  , Title VARCHAR(100)
);
CREATE TABLE Project (
    ProjectId INT NOT NULL IDENTITY(33,1)
  , Title VARCHAR(100)
);
 
MERGE INTO Project AS Target
USING tvp AS SOURCE
  ON 1 = 0
WHEN NOT matched THEN
INSERT (Title)
  VALUES (SOURCE.Title)
output SOURCE.oid, Inserted.ProjectId, Inserted.Title
INTO #tmp;
 
SELECT * FROM #tmp;

https://stackoverflow.com/questions/43661917/tsql-merge-2-ids-into-temp-table

https://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at-the-same-time/#:~:text=Beginning%20with%20SQL%20Server%202008,operations%20in%20a%20single%20statement.&text=The%20MERGE%20statement%20basically%20merges,in%20the%20target%20or%20not.'

https://sqlrambling.net/2017/05/19/merge-output-and-action/

https://stackoverflow.com/questions/16847650/when-using-multiple-when-matched-statements-do-they-all-execute-or-does-only-o#:~:text=The%20MERGE%20statement%20can%20have,if%20the%20first%20is%20not.