Powershell Script to export/import SQL Server tables

Question

Hello,

I'm looking to automate a SQL Server export/import job. The tables are located on Azure SQL Server so I was thinking of using a PS script for automation purposes. The job will run on a monthly basis to copy files from a few tables in one database to the same tables into a database on a different server.

I'm looking for a starting point, and was having trouble finding anything online.

Any help would be greatly appreciated.

Thanks,
Frank

Answer 1

Hi FrankAnellia-9381,

You can try to use SqlBulkCopy with Powershell to copy data from tables in one database to the same tables into a database on a different server. Please refer to this article which might help.
You also can use powershell dbatools Copy-DbaDbTableData to copy data.
For example, to copy all the data from table dbo.test_table (2-part name) in database dbatools_from on sql1 to table test_table in database dbatools_from on sql2:

Copy-DbaDbTableData -SqlInstance sql1 -Destination sql2 -Database dbatools_from -Table dbo.test_table  

Please refer to Copy-DbaDbTableData for more details.

Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Google.
powershell sql server import
powershell sql server export

Answer 3

You can start with Invoke-SqlCmd - something like:

$sourceData = Invoke-SqlCmd -ServerInstance ... -Query "...";

You can then pipe that to Export-Csv to create a CSV file.

Then use BCP or BULK INSERT on the destination to read in the CSV file.