I use multiple Merge Scripts for CI/CD deployment a database. Dacpac contains Post Deployment Script which is used in Azure Devops Deploy Azure DB Task. This script is a concatenation of multiple merge scripts. I would like to somehow store detailed log of which records was updated/deleted/inserted. In azure release logs I only have an information, that update script was executed (after enabling diagnostics).
Every mergescript is like this:
MERGE INTO [Common].[Address] AS Target
USING (VALUES
(N'00000009-0000-0000-0000-000000000001', N'Sample Street', N'Warsaw', N'15-123', N'Mazowieckie', N'Poland')
)
AS Source ([Id], [Street], [City], [PostalCode], [Voivodeship], [Country])
ON Target.[Id] = Source.[Id]
-- update matched rows
WHEN MATCHED AND (
[Target].[Street] != Source.[Street] OR
[Target].[City] != Source.[City] OR
[Target].[PostalCode] != Source.[PostalCode] OR
[Target].[Voivodeship] != Source.[Voivodeship] OR
[Target].[Country] != Source.[Country]
) THEN
UPDATE SET
[Street] = Source.[Street],
[City] = Source.[City],
[PostalCode] = Source.[PostalCode],
[Voivodeship] = Source.[Voivodeship],
[Country] = Source.[Country]
-- insert new rows
WHEN NOT MATCHED BY TARGET THEN
INSERT ([Id], [Street], [City], [PostalCode], [Voivodeship], [Country])
VALUES (Source.[Id], Source.[Street], Source.[City], Source.[PostalCode], Source.[Voivodeship], Source.[Country])
OUTPUT $action, [deleted].*, [inserted].*;
In the output I have the exact information that I need.
I can create dedicated temporary table, list all needed columns, then insert Output into this table. Then I can use FOR JSON Clause for whole rowset, and save it into another Log table that only have two columns: timestamp and json data, or just print this out. Problem with that soulution is that i have many merge scritps, and I don't want to create dedicated table for every one. Is there a way to initialize temporary table from Output Clouse? Or even serialize output directly into JSON formatted variable?
