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.
