DataTongs for SQL Server - Community

Overview

Now that you have your source and structure under source control and you've got your state based deployments in place it's time to manage your seed data in the same way. DataTongs will help you extract all or parts of your data and manage deployment via generated MERGE scripts using the data in a Json format. It connects to a SQL Server database and extracts data for the tables specified in the config file and will generate MERGE statements that insert missing rows and optionally update and delete based on the extracted data as well. You can define a filter clause in case you allow mixing user and system data in a single table and both the extraction and DELETE will honor that filter.

Simply point the application at a database with the correct seed data to capture, fill out the list of tables and the key columns for each, set the merge options you wish to generate and we'll handle generating the scripts for you. You can generate new scripts any time you need to update the seed data or manually update the script(s) if you find that simpler. Your application seed data will become part of your source control and state based updates.

Where DataTongs Fits

SchemaTongs extracts your schema into metadata.
Edit your metadata with your favorite text editor.
SchemaQuench applies your schema and data metadata to your SQL Servers.
DataTongs extracts your seed data into metadata

Quick Start Guide

1. Configure your appSettings.json

Point DataTongs at your source database and list the tables to extract. DataTongs generates standalone MERGE scripts that you can deploy via script folders or execute directly:

{
  "Source": {
    "Server": "localhost",
    "Database": "AdventureWorks",
    "User": "",
    "Password": ""
  },
  "OutputPath": "C:\\SchemaPackage\\Templates\\Main\\TableData",
  "Tables": [
    {
      "Name": "Person.AddressType",
      "KeyColumns": "",
      "Filter": ""
    },
    {
      "Name": "Person.ContactType",
      "KeyColumns": "",
      "Filter": ""
    },
    {
      "Name": "Sales.Currency",
      "KeyColumns": "",
      "Filter": "CurrencyCode IN ('USD','EUR','GBP')"
    }
  ],
  "ShouldCast": {
    "MergeUpdate": true,
    "MergeDelete": false,
    "DisableTriggers": false
  }
}

2. Run DataTongs

Execute DataTongs from the command line and tongs will begin the casting process.

> DataTongs

3. Deploy your scripts

Place the generated scripts in a TableData folder under your template to make them part of your state based deployment process, or execute them directly against your target databases.

See the DataTongs Walkthrough to help you get started with the tool.

Operating Modes

Community DataTongs generates standalone MERGE scripts. For content-file-based delivery with automatic table JSON updates and FK-aware deployment ordering, see DataTongs Enterprise.

Standalone Script Generation

DataTongs generates self-contained .sql files with MERGE statements. Each script is named Populate schema.tablename.sql and written to the OutputPath directory. These scripts can be placed in a TableData folder for SchemaQuench to run at deployment, or executed directly against your target databases.

The JSON data is embedded inline in each script. The script contains the extracted data in JSON format followed by a MERGE statement that parses the JSON with OPENJSON and applies inserts, updates, and/or deletes depending on your ShouldCast configuration.

Test Data

You can also use DataTongs to extract test data. Generate standalone scripts and execute them outside of SchemaQuench to populate test databases as part of your test pipeline. This simplifies integration test setup and lets you easily reset to a consistent starting state between test runs.

Extraction Details

Column Auto-Detection

DataTongs queries INFORMATION_SCHEMA.COLUMNS and sys.columns to build the column list automatically. Computed columns and ROWGUIDCOL columns are excluded. Geography columns are split into two expressions for WKT and SRID extraction.

When KeyColumns is left blank, DataTongs auto-detects match columns using the table's primary key. If no primary key exists, the unique index with the fewest columns is selected, with preference for indexes where all columns are NOT NULL.

Excluded Columns

The following column types are automatically excluded from extraction:

Column Type	Reason
Computed columns	Values are calculated by the database engine and cannot be inserted or updated directly.
`ROWGUIDCOL` columns	Globally unique identifiers managed by the database; excluded from both extraction and merge scripts.

Special Type Handling

Certain SQL Server data types require special handling during extraction and merge script generation:

SQL Server Type	Extraction	OPENJSON Mapping
`GEOGRAPHY`	Split into two JSON properties: `[Col].ToString()` for the WKT representation and `[Col].STSrid` for the spatial reference ID. Reconstructed via `geography::STGeomFromText()`.	`NVARCHAR(4000)` + `INT`
`XML`	Extracted as-is. Preserves schema collection when present.	`XML` or `XML([schema].[collection])`
`NTEXT`	Extracted as-is (deprecated type).	`NVARCHAR(MAX)`
`TEXT`	Extracted as-is (deprecated type).	`VARCHAR(MAX)`
`IMAGE`	Extracted as-is (deprecated type).	`VARBINARY(MAX)`
`HIERARCHYID`	Extracted as string representation.	`NVARCHAR(4000)`

For types that require casting during comparison (XML, NTEXT, TEXT, IMAGE), DataTongs generates NULL-safe change detection in the MERGE's WHEN MATCHED clause: NOT (CAST(Target.[Col] AS TYPE) = CAST(Source.[Col] AS TYPE) OR (Target.[Col] IS NULL AND Source.[Col] IS NULL)). This ensures updates only trigger when values have actually changed.

Extraction SQL

DataTongs extracts table data using FOR JSON AUTO with NOLOCK hints:

SELECT CAST((
  SELECT [Col1], [Col2], ...
    FROM [schema].[tableName] WITH (NOLOCK)
    WHERE {filter}
    ORDER BY {keyColumns}
    FOR JSON AUTO) AS NVARCHAR(MAX))

The NOLOCK hint allows non-blocking reads during extraction, safe because DataTongs is read-only. Results are ordered by key columns for deterministic output and cleaner source control diffs.

The JSON output is lightly formatted with newlines between row objects for readability, then embedded directly in the generated merge script.

ShouldCast Flags Reference

Flag	Default	Description
`MergeUpdate`	`true`	Include `WHEN MATCHED THEN UPDATE` clause in generated MERGE scripts. Updates existing rows when key columns match and values differ.
`MergeDelete`	`true`	Include `WHEN NOT MATCHED BY SOURCE THEN DELETE` clause. Deletes rows in the target not present in the source data. Honors the `Filter` predicate.
`DisableTriggers`	`false`	Wrap MERGE statements with `ALTER TABLE ... DISABLE TRIGGER ALL` before execution and `ENABLE TRIGGER ALL` after.

For additional ShouldCast flags including content file output, tokenized scripts, and automatic data delivery configuration, see DataTongs Enterprise.

Additional Resources

DataTongs Walkthrough - A step-by-step guide for casting data from a sample product. How-to Configuration Settings - Explore the configuration options available in appSettings.json. Reference SchemaTongs - Learn how to cast your databases. Related SchemaQuench - Learn how to quench your databases. Related Cast data with Ask Forge - Run DataTongs through the AI-powered CLI. AI Assistant

DataTongs - SQL Server (Community)