██████╗ ██████╗ █████╗ ████████╗ ██████╗ ██████╗ ██╗ ███████╗
██╔══██╗██╔══██╗██╔══██╗╚══██╔══╝██╔═══██╗██╔═══██╗██║ ██╔════╝
██║ ██║██████╔╝███████║ ██║ ██║ ██║██║ ██║██║ ███████╗
██║ ██║██╔══██╗██╔══██║ ██║ ██║ ██║██║ ██║██║ ╚════██║
██████╔╝██████╔╝██║ ██║ ██║ ╚██████╔╝╚██████╔╝███████╗███████║
╚═════╝ ╚═════╝ ╚═╝ ╚═╝ ╚═╝ ╚═════╝ ╚═════╝ ╚══════╝╚══════╝

██████╗ ███████╗ ██████╗██╗██████╗ ███████╗ ██╗ ██╗ ██████╗ ██████╗ ██╔══██╗██╔════╝██╔════╝██║██╔══██╗██╔════╝ ████████╗██╔═████╗╚════██╗ ██████╔╝█████╗ ██║ ██║██████╔╝█████╗ ╚██╔═██╔╝██║██╔██║ █████╔╝ ██╔══██╗██╔══╝ ██║ ██║██╔═══╝ ██╔══╝ ████████╗████╔╝██║ ╚═══██╗ ██║ ██║███████╗╚██████╗██║██║ ███████╗ ╚██╔═██╔╝╚██████╔╝██████╔╝ ╚═╝ ╚═╝╚══════╝ ╚═════╝╚═╝╚═╝ ╚══════╝ ╚═╝ ╚═╝ ╚═════╝ ╚═════╝

</pre>

Recipe #03 - Let's cook!

Main course:

- Copy data between tables

Under the hood it uses SQLBulkCopy which is one of the most efficient ways to copy batchs of data between tables

Set variables



In [ ]:

    
$dbatools1 = "localhost,1433"
$dbatools2 = "localhost,14333"
$secureString = ConvertTo-SecureString "dbatools.IO" -AsPlainText -Force
$cred = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList "sqladmin", $secureString

$sourceDB = "Northwind"
$destinationDB = "EmptyNorthwind"
$sourceTable = "[dbo].[Order Details]"

Create empty database on destination instance



In [ ]:

    
New-DbaDatabase -SqlInstance $dbatools2 -SqlCredential $cred -Name $destinationDB

Copy data

Note: Table do not exists so it will be created. However without PK, FK, UQ, (non)Clustered indexes..etc.
If you need to keep all the objects take a look at the “UPS…I HAVE DELETED SOME DATA. CAN YOU PUT IT BACK?” – DBATOOLS FOR THE RESCUE blog post to understand how you can create the object with same structure/properties before copying the data.



In [ ]:

    
# Copy all data within dbo.Categories to other instance
$copySplat = @{
    SqlInstance = $dbatools1
    SqlCredential = $cred
    Destination = $dbatools2
    DestinationSqlCredential = $cred
    Database = $sourceDB
    DestinationDatabase = $destinationDB
    Table = $sourceTable
    AutoCreateTable = $true 
    BatchSize = 1000
}
Copy-DbaDbTableData @copySplat

Check table content on destination



In [ ]:

    
Invoke-DbaQuery -SqlInstance $dbatools2 -SqlCredential $cred -Database $destinationDB -Query "SELECT TOP 10 * FROM $sourceTable" | Format-Table

Another example

Copy data based on a query



In [ ]:

    
# Copy specific data (see query parameter) from [dbo].[Order Details] to [dbo].[CopyOf_Order Details]
$copySplat = @{
    SqlInstance = $dbatools1
    SqlCredential = $cred
    Destination = $dbatools1
    DestinationSqlCredential = $cred
    Database = $sourceDB
    DestinationDatabase = $sourceDB
    Table = $sourceTable
    DestinationTable = "[dbo].[CopyOf_Order Details]"
    AutoCreateTable = $true 
    BatchSize = 1000
    Query = "SELECT * FROM $sourceDB.$sourceTable WHERE Quantity > 70 "
}
Copy-DbaDbTableData @copySplat

Confirm that data is there



In [ ]:

    
Invoke-DbaQuery -SqlInstance $dbatools1 -SqlCredential $cred -Database $sourceDB -Query "SELECT * FROM [dbo].[CopyOf_Order Details]" | Format-Table