Set start value for identity columns in MS SQL server automatically

Overview

In our example, we want to set the start value of an IDENTITY column automatically for newly created tables, based on the maximum value of the identity column in the source table.

This is necessary when creating the structure (DDL: tables, indices, references, etc.) of an SQL Server database containing approximately 30 tables into a new database without copying the corresponding data. The main objective is to ensure that synthetic data generated in the new database does not conflict with selectively loaded data from the source system. We use a structure compare task to archive this.

To achieve this, the IDENTITY column of each table in the target database must start at the maximum value found in the source table plus one. This process should be repeatable, accommodating changes in table count and record numbers without requiring manual adjustments.

Prerequisites

To implement this approach, the following components are required:

An SQL Server database containing source tables with IDENTITY columns.

Process Steps

We will create a task stage hook to identify the IDENTITY columns, to determine the maximum value of this column and to store the corresponding start value in the runtime properties.

Determine selected tables from a report

First, we have to identify the tables selected in Stage 1 of the task. For this we use the objectSelectorTableReport . For every table listed in the report the identity column will be identified. The complete hook can be found here.

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()
        ...
    }
}

Select maximum value and store start value

Afterwards, we have to select the maximum value of the identity column, calculate the start value and store the value as a runtime property. If no data is in the table, the value in the runtime property is set to 1.

        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 '${columnName}' in table ${tableSchema}.${tableName}: ${result}"
                int startValue
                if (result != null) {
                    startValue = Integer.parseInt(result) + 1
                } else {
                    startValue = 1
                }

                println "Start value for identity column '${columnName}' in table ${tableSchema}.${tableName}: ${startValue}"
                properties.put(tableName, startValue)
            }
        }

Define a Database Specific Mapping Rule

The stored property is used in a mapping rule.

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

The rule utilizes JavaScript to fetch the stored runtime property and apply it during the mapping process. It is defined as follows:

Field Selection Mode

Database specific

Object Type

IDENTITY_COLUMNS

Field Type

SEED_VALUE

Source selection pattern

%

Target value mode

Java script

Target Value

Java script 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 mapping rule must be adjusted accordingly.

Benefits

Automation: The process eliminates the need for manual adjustments when the database structure or record count changes.

Consistency: Ensures that new synthetic data does not overlap with selectively loaded data from the source system.

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

Minimal Configuration: No need to pre-extract and manually adjust maximum values.

Conclusion

This approach provides an efficient and automated method to copy SQL Server database structures while ensuring IDENTITY column consistency. By leveraging XDM Task Stage Hooks and Database Specific Mapping Rules, manual intervention is minimized, making the process scalable and adaptable to future changes.