Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- MERGE (targetTable) AS t
- USING (sourceTable) AS s
- ON t.ID = s.ID
- WHEN MATCHED THEN
- (some statements)
- WHEN NOT MATCHED THEN
- (some statements)
- OUTPUT
- $action, inserted.ID 'inserted', deleted.ID 'deleted'
- ;
- DECLARE @tableVar TABLE (MergeAction VARCHAR(20), InsertedID INT, DeletedID INT)
- MERGE (targetTable) AS t
- USING (sourceTable) AS s
- ON t.ID = s.ID
- WHEN MATCHED THEN
- (some statements)
- WHEN NOT MATCHED THEN
- (some statements)
- OUTPUT
- $action, inserted.ID 'inserted', deleted.ID 'deleted' INTO @tableVar
- ;
- SELECT MergeAction, COUNT(*)
- FROM @tableVar
- GROUP BY MergeAction
- declare
- @mergeResultsTable table (MergeAction VARCHAR(20));
- declare
- @insertCount int,
- @updateCount int,
- @deleteCount int;
- merge ...
- output $action into @mergeResultsTable;
- select @insertCount = [INSERT],
- @updateCount = [UPDATE],
- @deleteCount = [DELETE]
- from (select 'NOOP' MergeAction -- row for null merge into null
- union all
- select * from @mergeResultsTable) mergeResultsPlusEmptyRow
- pivot (count(MergeAction)
- for MergeAction in ([INSERT],[UPDATE],[DELETE]))
- as mergeResultsPivot;
- INSERT YourResultsTable (action, cnt)
- SELECT action, count(*)
- FROM
- (
- MERGE (targetTable) AS t
- USING (sourceTable) AS s
- ON t.ID = s.ID
- WHEN MATCHED THEN (some statements)
- WHEN NOT MATCHED THEN (some statements)
- OUTPUT $action as action, inserted.ID as ins, deleted.ID as del
- ) m
- GROUP BY action;
- INSERT YourResultsTable (action)
- SELECT action
- FROM
- (
- MERGE (targetTable) AS t
- USING (sourceTable) AS s
- ON t.ID = s.ID
- WHEN MATCHED THEN (some statements)
- WHEN NOT MATCHED THEN (some statements)
- OUTPUT $action as action, inserted.ID as ins, deleted.ID as del
- ) m;
Add Comment
Please, Sign In to add comment