question

NicholasPletch-0812 avatar image
0 Votes"
NicholasPletch-0812 asked AmeliaGu-msft commented

SSMS v18.9.2 Bug in auto generate scripts and temporal tables

Not sure where to put in a bug for this product so I will leave it here. Apologies if its not tagged right.

Using the generate script tool in SSMS, when copying temporal tables with the "Types of data to script" option set to "Schema and data".

The inserts it generated includes the start and end date time columns that are auto generated as a temporal table.

This causes the script to fail.

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

It is easy enough to remove the data on small tables or change the script. But for large generates it gets cumbersome and defeats the purpose of having an easy way of generating scripts.

Thanks!

sql-server-general
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


The INSERT statements are probably correct because time values are important too.

0 Votes 0 ·

Yes, I think Viorel is right. The correct script would create the insert all data directly into the history table, so that you get those dates correct. The current data would have to be inserted into the main table, and only then system_versioning would be turned on.

That is certainly not a trivial operation. Then again, I agree that producing a script that fails is quite useless.

0 Votes 0 ·

Hi NicholasPletch-0812,

Did the answers help you?
Please feel free to let us know if you have any other question.
If you find any post in the thread is helpful, you could kindly accept it as answer.

Best Regards,
Amelia

0 Votes 0 ·

1 Answer

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

Hi NicholasPletch-0812,
Welcome to Microsoft Q&A.
Unfortunately, there is a limitation that we cannot insert values into PERIOD columns (SysStartTime column and SysEndTime column) in the temporal tables.
If you want to insert values into PERIOD columns, you can switch SYSTEM_VERSIONING OFF and drop the PERIOD FOR SYSTEM_TIME first, and after inserting values, you can add the PERIOD FOR SYSTEM_TIME and Switch the SYSTEM_VERSIONING ON.
Please refer to Modifying data in a system-versioned temporal table and Seeding Temporal Tables which might be helpful.

Best Regards,
Amelia


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.