InfoSphere Optim

 View Only

 Insert Into SQL Server Versioned Table

Keith Tidball's profile image
Keith Tidball posted Tue December 02, 2025 12:44 PM

We are trying to define a new Column Map against a Versioned (temporal) Table in SQL Server 2019. The DATETIME column values for the versioning are auto generated, so they should be left as Default. However, I am not seeing any way to indicate 'Default' value in the Source column to satisfy the Destination columns controlling the versioned timestamp values. Yes, we can use a Timestamp register in the source column to appease the Editor window, but the INSERT fails as it is not expecting any values passed via the INSERT. There is no way to remove the columns from the CM, either, as Optim won't allow it. 

I have not seen any indication that Optim supports allowing for a Defaulted DBMS-level value that is autogenerated at INSERT time. However, I am hoping maybe I am missing something here. Has anyone else dealt with successfully inserting rows into temporal tables using Optim v11.7 LUW? 

We are running Optim TDM v11.7.0.2

Thanks for any insight on this. 

-Keith

kevin hampson's profile image
kevin hampson posted Tue January 06, 2026 04:12 PM

Hi Keith,

Sorry for the delay in getting back to you on this question. You are not missing anything in your testing. For one, Optim does not support SQL Server Temporal tables at this time. Most importantly though, the sys.columns view for the columns in the temporal table can return a  1 or 2 in the generated_always_type. With no support for temporal tables we ignore the values at this time. Because of this, the column map processing does not recognize that this is a "generated always" type column, and believes that a value can be specified.

I hope this helps answer the question,

-Kevin 

Related Content