Copy-DBADbTableData - Ignore Computed Columns

[AndrewBuis]

Verified issue does not already exist?

Yes

What error did you receive?

Currently, the Copy-DBADbTableData will attempt to copy all columns from the source table to the destination table. It doesn't work when the destination table contains a computed column. This will prevent any data from copying to the destination, giving a warning (not a error): WARNING: [11:16:33][Copy-DbaDbTableData] Something went wrong | The column "ComputedColumnName" cannot be modified because it is either a computed column or is the result of a UNION operator.

Steps to Reproduce

SQL to be ran on source & destination databases:

CREATE TABLE TestTable
(
    Dt DATETIME
    , DtDay AS (DATEPART(DAY, Dt))
    , DtMonth AS (DATEPART(Month, Dt))
    , DtYear AS (DATEPART(Year, Dt))
)

INSERT TestTable (Dt) VALUES (GETDATE()), (DATEADD(MONTH, -1, GETDATE()))

PowerShell: Copy-DbaDbTableData -SQLInstance $SrcServer -Destination $DestServer -Database $SrcDatabase -DestinationDatabase $DestDatabase -Table "TestTable" -DestinationTable "TestTable" -Truncate -KeepNulls

Are you running the latest release?

Yes

Other details or mentions

No response

What PowerShell host was used when producing this error

Windows PowerShell (powershell.exe)

PowerShell Host Version

Name Value

---

PSVersion 5.1.19041.1320
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.19041.1320
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

SQL Server Edition and Build number

Microsoft SQL Server 2019 (RTM-CU14) (KB5007182) - 15.0.4188.2 (X64) Nov 3 2021 19:19:51 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)

.NET Framework Version

Method invocation failed because [System.Runtime.InteropServices.RuntimeInformation] does not contain a method named 'get_FrameworkDescription'. At line:1 char:1

• [System.Runtime.InteropServices.RuntimeInformation]::get_FrameworkDes ...

• 
  + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
  + FullyQualifiedErrorId : MethodNotFound

[niphlod]

that's the same thing would happen for bulk load (which is what Copy-DbaDbTableData uses) so .... what would you expect ?

[AndrewBuis]

I know you can use the query parameter for it to use instead of the default "SELECT * FROM TestTable". So instead, use something like this query as the default instead:

SELECT 'SELECT ' + STRING_AGG(c.name,', ') + ' FROM ' + T.name
FROM sys.columns AS C
JOIN sys.tables AS t
ON C.object_id = t.object_id
WHERE C.is_computed = 0
AND t.name = 'TestTable'
GROUP BY t.name

[AndrewBuis]

Or add that as a new option -IgnoreComputedColumns in order to not change legacy scripts.

[niphlod]

the problem is not on the source, but on the dest. selecting from computed columns has no limits (e.g. your source has computed columns, and the dest doesn't, but you want all the data on the dest), the problem is that inserting into them is not possible. And, "legacy scripts" are definitely not to worry about, since this is a request for a new feature, not a bug.

[niphlod]

Adding that the same "feature" should reside also on Write-DbaDbTableData's side, which has the same underlying limits. IMHO a better suited "please copy the data over the best you can, I dunno even what can be written" function should be created which can be written to accomodate this and any other subtle intricacies, accepting all the necessary performance penalties, of dealing with copying data (i.e. even if types do not match, etc).

[braest]

Hi all, I was experimenting the past weekend with this. When I do : Invoke-DbaQuery -SqlInstance $SQLSourceServer -Database $SQLSourceDbase -Query $ExportQuery.statement -As DataSet Write-DbaDbTableData -SqlInstance $SQLTargetServer -Database $SQLTargetDbase -Table $TableToCopy.tablename

It works(!) but it's not ideal - first read, then write ; $ExportQuery.statement SELECTS all columns BUT the computed ones...

When I try Copy-DbaDbTableData ; using same -Query as before, no dice. Famous error about computed columns. Guessing it still tries to map the destination columns, however they are not available in the sourcequery.

So I'm wondering, can the same be ported to Copy-DbaDbTableData please? Or not so easy? Many thanks!