How to best generate script with data for system-versioned temporal tables?

105 Views Asked by At

In SQL Server 2022 I have tables with a column using the "GENERATED ALWAYS AS ROW START" feature for timestamps. When I generate scripts for these tables that include data, the insert statements don't work:

Msg 13536, Level 16, State 1, Line 1
Cannot insert an explicit value into a GENERATED ALWAYS column in table. Use INSERT with a column list to exclude the GENERATED ALWAYS column, or insert a DEFAULT into GENERATED ALWAYS column.

I am left having to remove the columns from the insert statement or change the values to DEFAULT. Is there a better way to handle this either in the table schema or somehow generate the script excluding those columns?

I am using C# with the SMO library to generate the scripts programmatically but I don't see a scripting option that applies to these columns.

Example of table column schema:

[RowUpdated] datetime2 GENERATED ALWAYS AS ROW START NOT NULL

How I am currently handling the script output:

var lScript = lTable.EnumScript(lScriptOptions);
            StringBuilder lOutput = new StringBuilder();
            foreach (String line in lScript)
            {
                lOutput.AppendLine(Regex.Replace(line, @"(CAST\(N)(.{1,40})(AS DateTime2\))", "DEFAULT"));
                lOutput.AppendLine("GO");
            }
            File.AppendAllText(aFilePath, lOutput.ToString());
0

There are 0 best solutions below