Copy-DbaDbTableData -Query silently miscounts computed columns without -ForceExplicitMapping, corrupting data

[JankeUwe]

Environment: dbatools 2.7.1 and 2.8.2 (reproduced on both), SQL Server

Description:
When the destination table has a computed column (e.g. at ordinal position 3) and Copy-DbaDbTableData is used in -Query mode without -ForceExplicitMapping, the cmdlet falls back to implicit ordinal column mapping that appears to count the computed column as a real target slot. Every column after it silently shifts by one position. No error or warning is produced - values land in the wrong columns.

Repro:

• Destination table with 108 columns, one computed column at ordinal position 3.
• Source query returns matching non-computed columns in order.
• Run Copy-DbaDbTableData -SqlInstance <src> -Database <db> -Query "SELECT ..." -DestinationSqlInstance <dst> -DestinationDatabase <db> -DestinationTable <table> without -ForceExplicitMapping.
• After copy, two locale-ID columns (GV_Source_ID/GV_Produkt in our schema) land swapped/wrong (0 vs 1033) - confirmed by direct row inspection, not a formatting artifact.

Expected:
Either exclude computed columns from ordinal counting by default, or clearly document that -ForceExplicitMapping is required whenever the destination has any computed column - ideally with a loud warning when it's omitted and a computed column is detected on the target.

Workaround used:
Built a custom SqlBulkCopy wrapper with explicit name-based ColumnMappings, bypassing -Query mode's implicit mapping entirely.

[andreasjordan]

Thanks for the precise report - reproduced on development, with one correction to the mechanism.

Without -ForceExplicitMapping the command adds no column mappings at all and lets SqlBulkCopy map by position against every column of the destination table. A computed column in that list makes the server reject the insert (error 271, "cannot be modified because it is either a computed column..."), so on our stack (dbatools.library with Microsoft.Data.SqlClient 6.1.5, SQL Server 2022 to 2025) a computed column fails loudly. The silent shift you describe is exactly what a rowversion (timestamp) column produces: SqlBulkCopy drops it from its list together with the source column that lands on it, everything behind moves one position to the left, the last column stays empty, and the command reports success. Does your destination table have a rowversion column near ordinal position 3, or a different SqlClient version? Either would explain the difference.

The fix makes the command build the positional mapping itself: query column n goes to the n-th writable destination column, with computed and rowversion columns skipped. An identity placeholder keeps working as documented. A query that returns more columns than the destination can take is now refused with a message naming both counts instead of dropping the surplus.

PR to follow.

created by Claude and reviewed by Andreas Jordan

[JankeUwe]

Checked our schema directly (dev/test copy of the same database on our lab instance, not the live production box, but it's the same table definition): no rowversion/timestamp column exists anywhere in Ergebnis_agg - 108 columns, zero timestamp-typed columns. The computed column really is at ordinal position 3 (ID_Match, varchar). The two columns that landed wrong in our original report (GV_Produkt/GV_Source_ID) sit at positions 15/16 - 12-13 columns downstream of the computed column, consistent with a one-position shift starting there rather than a rowversion-drop elsewhere in the table.

So in our case it does look like the computed column itself causing the silent shift, not a rowversion column. Happy to pull any other diagnostic info you need - SqlClient version we were on is Microsoft.Data.SqlClient (dbatools.library bundled), dbatools 2.7.1 and 2.8.2 both reproduced it.

[andreasjordan]

Thank you for checking the schema - that settles it, and it corrects me: with your driver/server combination the computed column itself is dropped silently from the implicit mapping, the same way a rowversion column is on ours. So "a computed column fails loudly" is not universal, it depends on the SqlClient/SQL Server versions involved, and your one-position shift starting at the computed column is fully consistent with that.

The good news: this makes no difference for the fix. #10666 stops relying on the implicit mapping of SqlBulkCopy entirely - the command now builds the positional mapping itself and maps query column n onto the n-th writable destination column, with computed, rowversion and generated always columns skipped. Your scenario is covered no matter which of the two silent-drop behaviors the driver shows, and a query with more columns than the destination can take is refused with a clear message instead of being truncated.

No further diagnostics needed from your side. The PR is in review; once it is merged the fix will be in the next release.

created by Claude and reviewed by Andreas Jordan