===== SQL MERGE y OUTPUT =====

<code sql>


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)

</code>
[[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]]

<code sql>
--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
</code>
  * [[https://www.dotnetcr.com/merge-en-sql-server-insert-delete-update/]]

==== Truco para usar la data tanto en el input y en el output ====

<code sql>
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); 
</code>
  * [[https://stackoverflow.com/questions/41184310/insert-into-merge-select-sql-server]]

Buen uso de los ouput:
<code sql>
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
</code>
[[https://stackoverflow.com/questions/17165870/multiple-output-clauses-in-merge-insert-delete-sql-commands]]

===== Merge Into ======

<code sql>
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;
</code>

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



===== Merge Into ======

<code sql>
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;
</code>

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

===== OUTPUT CON CASE WHEN =====

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

===== Merge no usa multiple matched =====

[[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.]]