Run query to copy table data from server to server without linked server by using powershell

Question

Run query to copy table data from server to server without linked server by using powershell

Mario2286 441

I would like to copy table data from a server to different server without using linked server, i want to use powershell but i m getting some error when executing this scripts below . We have few database in source server which have same table and database start with name dam. eg dam_1, dam_2 . I need to copy all table data of dam database to destination server database table .

Query copy.sql which i m using to copy data is 'insert into select from' query

I believe something wrong with this powershell scripts below

$tableName = "dbo.t_glory"

$tableName2 = "dbo.t_must_glory

$SQLServer = "DB15","DB21"

$query = "C:\queryset\copy.sql"

$data = "select name from sys.databases where name like '%dam%'"

$dataset = Invoke-DbaQuery -SqlInstance $SQLServer -Database $data -Query $query -As DataSet

$dataset | Write-DbaDbTableData -SqlInstance 'DB6' -Database mesh -Table $tableName2

User's image

Accepted answer

0 additional answers

Your answer

Answer 1

Javier Villegas 905 MVP

Hello

in your script your are missing a closing double-quote

$tableName2 = "dbo.t_must_glory**"**

here is the full script. it should work

$tableName = "dbo.t_glory"

$tableName2 = "dbo.t_must_glory"

$SQLServer = "DB15","DB21"

$query = "C:\queryset\copy.sql"

$data = "select name from sys.databases where name like '%dam%'"

$dataset = Invoke-DbaQuery -SqlInstance $SQLServer -Database $data -Query $query -As DataSet

$dataset | Write-DbaDbTableData -SqlInstance 'DB6' -Database mesh -Table $tableName2

Javier Villegas 905 Reputation points MVP

2023-08-18T11:35:18.73+00:00

not sure why it put the asterisks. here is the line missing the closing double-quote

$tableName2 = "dbo.t_must_glory"
Mario2286 441 Reputation points

2023-08-21T00:45:42.67+00:00

@Javier Villegas , Thank you its working now, i missed the closing double quotes.

I have another issue, i m getting error as below , i need to copy data from multiple database with same table name but it seems it requesting database name, any ways that we can amend this powershell scripts

Share via

Run query to copy table data from server to server without linked server by using powershell

0 additional answers

Your answer