Transfer a Table From One SQL Server instance to Another using PowerShell

Using Powershell, we can transfer a table with data from one SQL Server instance to another SQL Server instance.

Run Powershell as Administrator or ISE as Administrator.

If your system/server is not having dbatools, use the below command to install dbatools on your local system or on the server:

Install-Module -Name dbaTools

Now we can use the DBATools command for database-related activities.

To copy a table from one SQL Server instance to another SQL Server instance, use the copy-dbadbtabledata command.

Follow the below steps for this activity: 

Step 1: Run PowerShell as Administrator on the source SQL Server instance.

Step 2: Type the below command & run it in the Powershell window

Copy-DbaDbTableData 

-SqlInstance MyServer1 

-Destination MyServer2 

-Database AdventyreWorks2016 

-DestinationDatabase AdventureWorksDW2016 

-Table Person.BusinessEntityNew

Step 3: Now check the Destination Server.

Another Way:-

Step 1: Run PowerShell as Administrator on the source Server.

Step 2: Type the below command & run it in the Powershell window

Copy-DbaDbTableData 

-SqlInstance MyServer1 

-Destination MyServer2 

-Database AdventyreWorks2016 

-Table BusinessEntity

-FilePath "C:\BusinessEntity.txt"

Step 3: Now copy BusinessEntity.txt file on the destination Server for next step.

Step 4: Run PowerShell as Administrator on the destination Server.

Step 5: now run the below command to import data in the destination table: 

Import-DbaCsv 

-SqlInstance MyServer2 

-Database AdventyreWorksDW2016 

-Table BusinessEntityNew

-FilePath "C:\BusinessEntity.txt" 

-Delimiter "`t" 

-FirstRowContainsHeader $false

Above command will copy the data from C:\BusinessEntity.txt file to BusinessEntityNew table which is available in AdventyreWorksDW2016.

Note: Keep in mind to put actual value for SqlInstance, Destination, 

Database, Table, FilePath, DestinationDatabase parameters.