Create table as "$($SrcTable)_$($SrcServer)"

[gamarcon]

Hello I need assistance, please, to create the table on the $DestServer as "$($SrcTable)_$($SrcServer)" . I am attempting to automate the copy of databases and save some tables prior to the refresh.

Please assist. Thank you

[DetectiveEric]

Hello, There's an optional parameter $DestTable you can use to overwrite the default behavior of keeping the source table name. Does that work for your use case?

Copy-SqlTable.ps1 -DestTable "$($SrcTable)_$($SrcServer)" ...

[gamarcon]

Hello. It did not. Unfortunately – I tried that before. What I am trying to do: Automate the refresh from PRD to DEV; But there is tables in DEV that need to be kept; so I am copying them to the TEST db and them, I’ll insert the data back on the restored DBs in DEV. Hence, I am copying them and labeling as source_table

I altered the PS script and added

[string] $DestTable = "$($SrcTable)_$($SrcServer)",

And pulled the script as below – It is supposed to create if it does not exist or truncate if it exists:

C:\TEST\PS\CopySqlTable.ps1 -SrcServer "SERVER01" -SrcDatabase DB01 -SrcTable TB01 -DestServer "SERVER02" -DestDatabase DB02 -Create -Truncate

When pulling it as above, I end up with error:

Cannot access destination table TB01_SERVER01. At C:\TEST\PS\CopySqlTable.ps1:240 char:2 + throw $err + CategoryInfo : OperationStopped: (1:Int32) [], RuntimeException + FullyQualifiedErrorId : 1

If I attempt to add the Destination table to the string like this:

C:\TEST\PS\CopySqlTable.ps1 -SrcServer "LPT-SUR-GMARCON" -SrcDatabase AdventureworksDW2016CTP3 -SrcTable DimProduct -DestServer "LPT-SUR-GMARCON\PANDA" -DestTable "$($SrcTable)_$($SrcServer)" -DestDatabase PANDA01 -Create -Truncate

I get another error:

Cannot access destination table '_'.

Even if I remove Truncate from the string it does not create the table. It expects the table to be created. I would like to know, please, where else should I alter the script in order to have the table created as "$($SrcTable)_$($SrcServer)"

Appreciate your time and assistance. Thanks a lot.

[gamarcon]

I ended up using dbatools where the table is created based on the Source and I can rename it. Still, I have several adjustments to do.

Param ( 

$SrcInstance = 'server01',
$SrcDatabase = 'DB01',
$SrcTable  =  'TB01',

$DestInstance  = 'server02',
$DestDatabase = 'DB02',
$DesTable = "$($SrcTable)_$($SrcInstance)",
[switch] $Truncate, 
$Sql = "SELECT * From " + $SrcTable
)

If ($Truncate) {  
    $TruncateSql = "TRUNCATE TABLE " + $DestTable 
    Sqlcmd -S $DestInstance -d $DestDatabase -Q $TruncateSql 
} 

$sourcetable = Invoke-SqlCmd2 -ServerInstance $SrcInstance -Database $SrcDatabase -Query $Sql  -As DataTable
Write-DbaDataTable -InputObject $sourcetable -SqlInstance $DestInstance -Database $DestDatabase -Table $DesTable -AutoCreateTable -Truncate 

I will continue to work on both scripts because I like yours better because it imports the attributes as well. Thank you