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());