-- MERGE
MERGE #Target t
USING #Source s
ON s.ID = t.ID
WHEN MATCHED
THEN UPDATE
SET Value = s.Value
WHEN NOT MATCHED
THEN INSERT
( ID, Value )
VALUES
( s.ID
, s.Value
);
-- UPDATE/INSERT
BEGIN TRANSACTION T1;
UPDATE t
SET Value = s.Value
FROM #Target t
INNER JOIN #Source s
ON s.ID = t.ID;
INSERT INTO #Target
SELECT s.ID
, s.Value
FROM #Source s
LEFT JOIN #Target t
ON s.ID = t.ID
WHERE t.ID IS NULL;
#################################
insert into [SubProjSchema].[Products]([ProductName],[ProductId],[UnitPrice],[CreatedBy])
SELECT [SPTI].[ProductName]
,[SPTI].[ProductId]
,[SPTI].[UnitPrice]
,[User]
FROM @p1 [SPTI]
left join [SubProjSchema].[Products] [PRD] on [SPTI].[ProductId] = [PRD].[ProductId]
where [PRD].ProductId is null
insert into [SubProjSchema].[Products]([ProductName],[ProductId],[UnitPrice],[CreatedBy])
SELECT [SPTI].[ProductName]
,[SPTI].[ProductId]
,[SPTI].[UnitPrice]
,[User]
FROM @p1 [SPTI]
WHERE NOT EXISTS(SELECT 1 FROM [SubProjSchema].[Products] [PRD] WHERE [SPTI].[ProductId] = [PRD].[ProductId])
#################################
COMMIT TRANSACTION T1;
