Copy-DbaDbTableData is not useful for system-versioned temporal tables

[ReeceGoding]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

No error. This is a result correctness issue.

Steps to Reproduce

Setup

$CreateTable = @'
CREATE TABLE dbo.WaitStats
(
    wait_type           SYSNAME NOT NULL PRIMARY KEY,
    waiting_tasks_count BIGINT  NOT NULL,
    wait_time_ms        BIGINT  NOT NULL,
    max_wait_time_ms    BIGINT  NOT NULL,
    signal_wait_time_ms BIGINT  NOT NULL,

    ValidFrom           DATETIME2(2) GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo             DATETIME2(2) GENERATED ALWAYS AS ROW END   NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = dbo.WaitStatsHistory,
        DATA_CONSISTENCY_CHECK = ON
    )
);
'@

$PopulateTable = @'
INSERT INTO dbo.WaitStats
    (wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms)
SELECT
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms
FROM sys.dm_os_wait_stats;
'@

$PopulateHistoryTable = @'
UPDATE dbo.WaitStats
SET waiting_tasks_count = waiting_tasks_count;
'@

$ConfirmPopulation = @'
SELECT TOP (10) *
FROM dbo.WaitStats
FOR SYSTEM_TIME ALL
ORDER BY wait_type, ValidFrom;
'@

$CommonQueryParams = @{
	SqlInstance = $ins
	Database = "TemporalSource"
}

New-DbaDatabase -SqlInstance $ins -Name TemporalSource, TemporalTarget
Invoke-DbaQuery @CommonQueryParams -Query $CreateTable
Invoke-DbaQuery @CommonQueryParams -Query $PopulateTable
Invoke-DbaQuery @CommonQueryParams -Query $PopulateHistoryTable
Invoke-DbaQuery @CommonQueryParams -Query $ConfirmPopulation

Execution

You are now ready to attempt Copy-DbaDbTableData.

Copy-DbaDbTableData @CommonQueryParams -DestinationDatabase TemporalTarget -Table dbo.WaitStats

but this fails and tell you to use -AutoCreateTable

WARNING: [timestamp here][Copy-DbaDbTableData] Table [dbo].[WaitStats] cannot be found in TemporalTarget. Use -AutoCreateTable to automatically create the table on the destination.

So you do

Copy-DbaDbTableData @CommonQueryParams -DestinationDatabase TemporalTarget -Table dbo.WaitStats -AutoCreateTable

which also fails because the table doesn't have a primary key.

WARNING: [timestamp here][Copy-DbaDbTableData] Unable to determine destination table: [dbo].[WaitStats] | System versioned temporal table 'TemporalTarget.dbo.WaitStats' must have primary key defined.

So you make the table...

Invoke-DbaQuery -Database TemporalTarget -SqlInstance $ins -Query $CreateTable
Copy-DbaDbTableData @CommonQueryParams -DestinationDatabase TemporalTarget -Table dbo.WaitStats

which totally fails

WARNING [timestamp here][Copy-DbaDbTableData] Something went wrong | Cannot insert an explicit value into a GENERATED ALWAYS column in table 'TemporalTarget.dbo.WaitStats'. Use INSERT with a column list to exclude the GENERATED ALWAYS column, or insert a DEFAULT into GENERATED ALWAYS column.

So we can try -Query in the way the error asks for.

$SelectOnly = @'
SELECT
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms
FROM dbo.WaitStats;
'@
Copy-DbaDbTableData @CommonQueryParams -DestinationDatabase TemporalTarget -Table dbo.WaitStats -Query $SelectOnly

and this works, but has not actually copied the data over. These two are not identical.

$SelectStar = 'SELECT * FROM dbo.WaitStats;'

Invoke-DbaQuery @CommonQueryParams -Query $SelectStar |
Sort-Object -Property wait_type |
Select-Object -First 5

Invoke-DbaQuery -SqlInstance $ins -Database TemporalTarget -Query $SelectStar |
Sort-Object -Property wait_type |
Select-Object -First 5

Please confirm that you are running the most recent version of dbatools

2.1.31

Other details or mentions

No response

What PowerShell host was used when producing this error

Windows PowerShell (powershell.exe), PowerShell Core (pwsh.exe)

PowerShell Host Version

PSVersion 7.5.2
PSEdition Core
GitCommitId 7.5.2
OS Ubuntu 24.04.2 LTS
Platform Unix
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1
WSManStackVersion 3.0

SQL Server Edition and Build number

I've seen this across multiple, but I'm on 2022 today.

.NET Framework Version

.NET 9.0.6

[niphlod]

Hi @ReeceGoding . -AutoCreateTable is not going to work for every shape of table, so that's not going to work for "advanced" things like System-Versioned.
If you look at the code it's actually very-really-super simple and straightforward: select * from table where 1=2 on the temp, export the script, create on the dest.
I'm not seeing Copy-DbaDbTableData in the near term supporting advanced scripting scenarios.

When you have the dest already, the copy goes through: it copies the select you pass, meaning you "loose" the "history of changes" .
I'm not understanding your point of

but has not actually copied the data over. These two are not identical

of course they aren't, they're system versioned...
are you expecting all records to match, including ValidFrom and ValidTo ?

[ReeceGoding]

are you expecting all records to match, including ValidFrom and ValidTo ?

Ideally, yes. I appreciate that it is very hard to accomplish.

[niphlod]

The whole point of System-Versioned tables is that it's the SYSTEM that VERSIONs them, not you ^_^
What you're trying to accomplish is, simply, unfeasible.

[niphlod]

unless, of course, your destination table is NOT system versioned, in which case you can do whatever you want (but the source query should output the whole history e.g. with FOR SYSTEM TIME ALL)

[ReeceGoding]

I think you've got me. What I've asking is somewhere between too hard and entirely unrealistic.

Is it worth giving any kind of warning or documentation about how Copy-DbaDbTableData reacts to temporal tables?