Skip to main content
Open

Create mock data based on system versioned table

Related products:Software Factory

At this moment we cannot use data sets in unit tests that are based on a system versioned table. It results in the error: ‘Truncate failed on table “...” because it is not a supported operation on system-versioned tables.’. As our product table is system versioned to show changes made to the articles, we cannot make any unit tests for control procedures that interact with the article table. We would like to see this implemented.

Did this topic help you find an answer to your question?

5 replies

Mark Jongeling
Administrator
Forum|alt.badge.img+23

Hi @BramG,

Awesome idea!, we currently are using the following workaround for the time being:

Preparation code

First off disabling the system versioning on the table, then inserting the necessary data into the history table, then re-enabling the system versioning.

Code we used as example:

declare @sql nvarchar(max) = ''

set @sql = 'alter table [TABLE] set ( system_versioning = off ); '
exec sp_executesql @sql

set @sql = 'insert into [TABLE]_history ([COLS], tsf_valid_from, tsf_valid_to) values ([COL_VALUES], [tsf_valid_from], [tsf_valid_to]);'
exec sp_executesql @sql

set @sql = 'alter table [TABLE] set ( system_versioning = on ( HISTORY_TABLE = dbo.[[TABLE]_history], DATA_CONSISTENCY_CHECK = ON));'
exec sp_executesql @sql; 

 

For the idea, we'll need to find a good way of integrating this in the data model so we can more easily make this happen. Let's gather some votes 😄


Mark Jongeling
Administrator
Forum|alt.badge.img+23
NewOpen

Forum|alt.badge.img+4
  • Author
  • Warrior
  • 38 replies
  • June 28, 2023

Hi! The last release event implied that this will be in 2023.2 release. Is that correct?


Sulea Ema
Vanguard
Forum|alt.badge.img+1
  • Vanguard
  • 16 replies
  • July 18, 2023

Hi @Mark Jongeling ! I have too many tables that are system versioned and enough data to insert into .There is another solution to use the mock data with the system versioned tables?


Mark Jongeling
Administrator
Forum|alt.badge.img+23
BramG wrote:

Hi! The last release event implied that this will be in 2023.2 release. Is that correct?

Not sure if we mentioned that, but we did add the ability to have mock data for Views. This idea remains open.

 

Sulea Ema wrote:

Hi @Mark Jongeling ! I have too many tables that are system versioned and enough data to insert into .There is another solution to use the mock data with the system versioned tables?

From 2023.2, you have the option to write a Mock data preparation query in which you can add data to the system-version tables. Sadly this is not that trivial yet so my example is still the only way for now.

History tables are not part of the Data model so it requires a bit more effort to implement. However, it's not impossible but we have not yet planned the implementation of this idea.

 


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings