dbatools is a free PowerShell module with over 300 SQL Server administration, best practice and migration commands included.

Please note that documentation and command names may be out of date while we work furiously towards 1.0


Copy-DbaTableData

Copies data between SQL Server tables using SQL Bulk Copy. Copying data can also be achieved by doing:

$sourcetable = Invoke-SqlCmd2 -ServerInstance instance1 ... -As DataTable
Write-DbaDataTable -SqlInstance ... -InputObject $sourcetable

but it will force buffering the contents on the table in memory (high RAM usage for large tables).
With this function, a streaming copy will be done in the speediest, least resource-intensive way.

Screenshots

dbatools-Copy-DbaTableData

Examples

Copies all the data from sql1 to sql2, using the database dbatools_from.

Copy-DbaTableData -SqlInstance sql1 -Destination sql2 -Database dbatools_from -Table test_table

Copies all the data from sql1 to sql2, using the database dbatools_from as source and dbatools_dest as destination.

Copy-DbaTableData -SqlInstance sql1 -Destination sql2 -Database dbatools_from -DatabaseDest dbatools_dest -Table test_table

Copies all data from tables tb1 and tb2 in tempdb on sql1 to tb3 in tempdb onsql1.

Get-DbaTable -SqlInstance sql1 -Database tempdb -Table tb1, tb2 | Copy-DbaTableData -DestinationTable tb3

Copies data from tbl1 in tempdb on sql1 to tbl1 in tempdb on sql2 then copies data from tbl2 in tempdb on sql1 to tbl2 in tempdb on sql2.

Get-DbaTable -SqlInstance sql1 -Database tempdb -Table tb1, tb2 | Copy-DbaTableData -Destination sql2

Copies all the data from sql1 to sql2, using the database dbatools_from.

Copy-DbaTableData -SqlInstance sql1 -Destination sql2 -Database dbatools_from -Table test_table

Copies all the data from sql1 to sql2, using the database dbatools_from, keeping identity columns and truncating the destination.

Copy-DbaTableData -SqlInstance sql1 -Destination sql2 -Database dbatools_from -Table test_table -KeepIdentity -Truncate

Copies all the data from sql1 to sql2, using the database dbatools_from, keeping identity columns and truncating the destination.

Copy-DbaTableData -SqlInstance sql1 -Destination sql2 -Database dbatools_from -Table test_table -KeepIdentity -Truncate

Author

This command was created by Simone Bizzotto. You can find Simone on Twitter.

Get-Help

From PowerShell, execute Get-Help Copy-DbaTableData -Detailed for more information on this function.

Source Code

Want to see the source code? View Copy-DbaTableData.ps1 on GitHub
 

Related commands