Add note to documentation for Copy-DbaDbTableData - if column is an identity value, a fake value needs to be supplied by the source

[chadbaldwin]

I don't think this is a bug of the cmdlet because this issue also exists with sqlcmd.exe / Invoke-SqlCmd.

But I think it would be extremely beneficial to anyone using this cmdlet if they knew ahead of time that if you have an identity column in your destination table, that you need to supply a "fake" value for that column.

Since the columns are mapped based on their order, and not by their names, if a column is an identity column, it still tries to map the source data columns to it.

But if you put a fake value in as a placeholder, the value will be ignored and the destination will set the identity value as expected.

Here's an example I'm currently working on:

CREATE TABLE syscollect.stage_map_schema_id (
    ID                  bigint          NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    [database_name]     nvarchar(128)   NOT NULL,
    [schema_name]       nvarchar(128)   NOT NULL,
);

$params = @{
  # Source
  SqlInstance = 'SERVER001'
  Database    = 'MyDatabaseName'
  View        = 'sys.schemas'
  Query       = 'SELECT 0, DB_NAME(), [name] FROM sys.schemas'

  # Destination
  Destination         = 'SERVER002'
  DestinationDatabase = 'MyOtherDatabaseName'
  DestinationTable    = 'syscollect.stage_map_schema_id'
}

Copy-DbaDbTableData @params

Without that fake value added to the Query, it will cause the insert to fail.