For adhoc data copy requests, I have always used the SSMS GUI to copy data from one instance to another. That is until I have recently thought to use dbatools to replace my workflow. I am glad I came across the Copy-DbaTableData command. This replaces my workflow with a one line command. The more I use dbatools, I love the simplicity of it and the constant improvements that go into this open source tool.
Copy-DbaDbTableData -SqlInstance SQLSERVER01 -Destination SQLSERVER02 -Database DB1 -Table dbo.test_data -DestinationTable dbo.test_data1 -AutoCreateTable
AutoCreateTable – Allows to create a table on destination database if it does not exist already
DestinationTable – Allows you to give a different table name if you dont want to use the same name as the source table
Copy-DbaDbTableData -SqlInstance SQLSERVER01 -Destination SQLSERVER02 -Database DB1 -Table dbo.test_data -DestinationTable dbo.test_data1 -Truncate -keepIdentity
Use the truncate option if you would like to truncate the destination table before copying the rows.
Use the keepidentity option if you would like to retain the identity column values in the destination table.
Database Blog 