Automating Identity Start Values

Overview

This guide describes how to automatically set the start value of SQL Server IDENTITY columns for newly created target tables.

In this example, the start value is calculated from the maximum value of the corresponding identity column in the source table.

This is useful when creating the database structure, such as tables, indexes, and references, in a new SQL Server database without copying all data.

The goal is to ensure that synthetic data generated in the new target database does not conflict with data that is selectively loaded from the source system.

To achieve this, the IDENTITY column of each target table starts at:

MAX(source identity column) + 1

If the source table does not contain data, the start value is set to 1.

This process is repeatable and does not require manual adjustments when the number of tables or records changes.

Prerequisites

  • An SQL Server source database with tables containing IDENTITY columns.

  • Sufficient database permissions to read metadata and table data from the source database.

Steps

Step 1: Create a Task Stage Hook

Create a Task Stage Hook that identifies the selected tables, detects their IDENTITY columns, calculates the required start values, and stores them as runtime properties.

The complete hook can be found here:

Calculate Start Value

Step 2: Determine Selected Tables from the Report

First, identify the tables selected in Stage 1 of the task.

For this, use the objectSelectorTableReport.

For every table listed in the report, the hook checks whether an identity column exists.

def reportFile = new File(properties.get('root_dir') + "/reports/Stage1_0_objectSelectorTableReport_Source.rpd")
def report = FileUtils.readFileToString(reportFile)
def reportData = ReportUtils.deserializeReportData(report)

try {
    for (int i = 0; i < reportData.rowCount; i++) {
        def row = reportData.getRow(i)
        def tableSchema = row[1]
        def tableName  = row[2]

        def query1 = """
            SELECT COLS.name AS COLNAME
            FROM sys.identity_columns COLS
            INNER JOIN sys.tables TABS ON COLS.object_id = TABS.object_id
            INNER JOIN sys.schemas SCH ON SCH.schema_id = TABS.schema_id
            WHERE SCH.name = '${tableSchema}'
              AND TABS.name = '${tableName}'
              AND COLS.is_computed = 0
            ORDER BY COLS.column_id
        """.trim()

        println "Executing statement: $query1"
        pStmt1 = taskStageHook.connection.jdbcConnection.prepareStatement(query1)
        rs1 = pStmt1.executeQuery()

        ...
    }
}

Step 3: Calculate and Store the Start Value

After the identity column has been identified, select its maximum value from the source table.

The start value is calculated as follows:

  • If the table contains data: MAX(identity column) + 1

  • If the table is empty: 1

The calculated value is stored as a runtime property.

if (rs1.next()) {
    def column = rs1.getString("COLNAME")
    def query2 = "SELECT MAX(${column}) AS VALUE FROM ${tableSchema}.${tableName}"

    pStmt2 = taskStageHook.connection.jdbcConnection.prepareStatement(query2)
    rs2 = pStmt2.executeQuery()

    if (rs2.next()) {
        def result = rs2.getString("VALUE")
        println "Max value for identity column '${column}' in table ${tableSchema}.${tableName}: ${result}"

        int startValue
        if (result != null) {
            startValue = Integer.parseInt(result) + 1
        } else {
            startValue = 1
        }

        println "Start value for identity column '${column}' in table ${tableSchema}.${tableName}: ${startValue}"

        properties.put(tableName, startValue)
    }
}
In productive scripts, database identifiers such as schema names, table names, and column names should be quoted or escaped correctly.

Step 4: Define a Database Specific Mapping Rule

The stored runtime property is used in a Database Specific Mapping Rule.

This rule applies to Microsoft SQL Server and sets the IDENTITY column start value in the target database.

Field Selection Mode

Database specific

Object Type

IDENTITY_COLUMNS

Field Type

SEED_VALUE

Source selection pattern

%

Target value mode

Java script

Target Value

JavaScript code

The following JavaScript snippet retrieves the runtime property and applies it to the corresponding table:

importPackage(Packages.de.ubs.xdm.utils.databasemodel.model.db);

tableName = baseObject.columnProvider.getName(PropertyObject.ApplyHooks.NO);
outputValue = Packages.de.ubs.xdm.utils.core.RuntimePropertiesUtil.get(tableName, "");
This implementation assumes that each table has a unique name and only one IDENTITY column. If multiple IDENTITY columns exist or table names are not unique, both the hook and the mapping rule must be adjusted.

Benefits

This approach provides the following benefits:

  • Automation: No manual adjustment of identity start values is required.

  • Consistency: Synthetic data in the target database does not overlap with selectively loaded source data.

  • Repeatability: The process can be executed multiple times without additional effort.

  • Minimal configuration: Maximum values do not need to be extracted and maintained manually.

  • Scalability: Changes in table count or record count are handled automatically.

Conclusion

This approach provides an efficient and automated way to create SQL Server database structures while keeping IDENTITY column values consistent.

By combining XDM Task Stage Hooks with Database Specific Mapping Rules, manual intervention is minimized.

This makes the process repeatable, scalable, and adaptable to future database changes.