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.

Import a Table Using SSIS Package

We can use an SSIS package with C# or VB script to create a package to transfer a table from one SQL Server instance to another SQL server Instance.

The steps to create a new SSIS package are given below:-

1. Open Visual Studio and create a new Integration Services project.

2. Right-click on the project and select "New Package" to create a new SSIS package.

3. Select "Data Flow Task" & put it on the package design surface.

4. To open the Data Flow tab, double-click on the "Data Flow Task".

5. Now select an "OLE DB Source" component & put it on the design surface.

6. To open the editor, double-click on the "OLE DB Source" component.

7. Now we need to set a few parameters for the "OLE DB Source" component. OLE DB source requires a data source from which we need to fetch the data or a table & transfer the to a new SQL instance.

How to import a table from an SQL Server into another SQL Server?

The way to import a table from one SQL Server to another SQL Server is very easy.

SQL Server provides a feature to Import and Export data or complete objects to another or even the same environment. That utility is Import and Export Wizard in SQL Server Management Studio (SSMS). The Import and Export Wizard provides the facility to import or export data/objects between different data sources like flat file, SQL Server, Sybase, MySQL, Oracle, Access and Excel etc.

There are multiple ways to import or export. A few of them are given below for your knowledge. Suppose we want to import a table from one SQL Server to another SQL Server:

Step 1: Connect to the Source SQL Server Instance.