Copy data between SQL server tables

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: