I have a SQL Server database where all of the tables are system-versioned tables.
I get a full history of all changes. I can live with the table designer not working in SSMS, but I ran into a bigger issue when scripting out data.
Right-click the database and select Tasks -> Generate Scripts… On the Set Script Options tab select Advanced and change “Types of data to script” to “Schema and data”. It generates a script containing lines like:
INSERT [dbo].[AspNetUserRolesHistory] ([UserId], [RoleId], [StartTime], [EndTime])
VALUES (N'b0cfa598-0c2d-4c27-8ef0-3fbdf7d41b17', N'16712460-a3fd-4bdc-aded-2b93901fc199',
CAST(N'2022-08-27T03:35:59.7402366' AS DateTime2),
CAST(N'2022-08-27T03:40:43.0736925' AS DateTime2))
When I run this on the target database I get the error:
Msg 13536, Level 16, State 1, Line 1
Cannot insert an explicit value into a GENERATED ALWAYS column in table 'dbo.AspNetUserRoles'.
Use INSERT with a column list to exclude the GENERATED ALWAYS column,
or insert a DEFAULT into GENERATED ALWAYS column.
It is going to be test data so I don't care about the [StartTime], [EndTime] columns and I can easily search and replace the column names out. But the data part for these columns varies with each line.
CAST(N'2022-08-27T03:35:59.7402366' AS DateTime2), CAST(N'2022-08-27T03:40:43.0736925' AS DateTime2)
Can’t easily search and replace them out because they are all different. I tried replacing “CAST(N’” with “) --“, but these aren’t the only DateTime columns and I have thousands of rows.
Any way to do this without having to go through Excel?