Data Delivery - SQL Server (Enterprise)

SchemaQuench dynamically generates a MERGE statement for each table. The content file data is loaded into a variable and parsed with OPENJSON. Column metadata is resolved from the target database at delivery time, not stored in the content file.

DECLARE @v_json NVARCHAR(MAX) = '[ ... content file data ... ]';

MERGE INTO [Schema].[TableName] AS Target
USING (
  SELECT [Col1], [Col2], ...
    FROM OPENJSON(@v_json)
    WITH (
           [Col1] NVARCHAR(100),
           [Col2] INT,
           ...
    )
) AS Source
ON Source.[KeyCol] = Target.[KeyCol]

WHEN MATCHED AND (NOT (Target.[Col2] = Source.[Col2]
      OR (Target.[Col2] IS NULL AND Source.[Col2] IS NULL))) THEN
  UPDATE SET [Col2] = Source.[Col2], ...

WHEN NOT MATCHED BY TARGET THEN
  INSERT ([Col1], [Col2], ...)
  VALUES (Source.[Col1], Source.[Col2], ...)

WHEN NOT MATCHED BY SOURCE THEN
  DELETE;

Certain SQL Server types cannot be used directly with OPENJSON. SchemaQuench automatically maps these types in the WITH clause and reconstructs the original values in the SELECT.

The WHEN MATCHED clause only triggers an update when at least one column value has actually changed. All comparisons use NULL-safe equality: if both the source and target values are NULL, the columns are considered equal.

MergeFilter

Only applies to the DELETE clause (WHEN NOT MATCHED BY SOURCE). Restricts which rows are eligible for deletion, leaving rows outside the filter untouched. The filter expression must reference the Target alias. For example, Target.[Region] = 'US' would only delete unmatched US-region rows.

MergeDisableTriggers

When set to true, the merge script is wrapped with ALTER TABLE [Schema].[Table] DISABLE TRIGGER ALL before execution and ALTER TABLE [Schema].[Table] ENABLE TRIGGER ALL after. This prevents triggers from firing during data synchronization.

Identity Columns

If the target table contains an identity column, SchemaQuench automatically wraps the merge script with SET IDENTITY_INSERT [Schema].[Table] ON and SET IDENTITY_INSERT [Schema].[Table] OFF. Identity columns are included in the content file and delivered as explicit values but excluded from the UPDATE clause since their values cannot change.

Content files are created by DataTongs, which queries the source database using FOR JSON AUTO with NOLOCK hints. DataTongs can also automatically configure the data delivery properties in your table JSON files when the ConfigureDataDelivery setting is enabled.

Column metadata is not stored in the content file. At delivery time, SchemaQuench queries the target database's INFORMATION_SCHEMA and sys.columns to resolve column types, nullability, and identity status. This means the content file is decoupled from the target schema -- columns can be added or removed from the table definition without regenerating the content file, as long as the match columns remain present.

The following column types are automatically excluded from content files and merge scripts:

GEOGRAPHY

Extracted as two properties per column: [Col].ToString() for the WKT representation and [Col].STSrid for the spatial reference ID. At delivery time, the value is reconstructed via geography::STGeomFromText([Col], [Col.STSrid]).

When MatchColumns is not specified, SchemaQuench auto-detects the best match columns from the target table using this priority:

1. Primary Key -- preferred if one exists.
2. Unique index -- selected by fewest columns, with preference for all non-nullable columns.

When nullable columns are part of the match key, they are prefixed with an asterisk in the MatchColumns value (e.g., *[NullableCol]). This tells SchemaQuench to generate IS NULL-safe equality in the ON clause: (Source.[Col] = Target.[Col] OR (Source.[Col] IS NULL AND Target.[Col] IS NULL)).