Using PowerShell to Run a SQL query then insert the results into a table

Question

I have a small PowerShell script that runs a query on about 30+ servers which pulls the server name and which version of SQL Server is installed. I then want to insert that data into a table but can't quite figure out how to do that with the returned data set my query returns. This is my code so far

$SvrNameList = @( invoke-sqlcmd -serverinstance MyServer -Database MyDB -Query "SELECT ServerName FROM ServerNames WHERE [Enabled] = 1"  ) | select-object -expand ServerName


foreach ( $i in $SvrNameList )
{
   invoke-sqlcmd -ServerInstance $i -Query "SELECT @@ServerName AS ServerName, @@Version AS Version" 
}

Any help is appreciated

Because that would try insert 1 line on each of the servers as it is running the query once on each server. I want to insert all of the results into a table on 1 server. — Cornflake2068
– Cornflake2068, Commented May 23, 2017 at 9:24

vonPryz · Accepted Answer · 2017-05-23 10:41:10Z

2

First, add the instance names and versions into a hash table. After it's populated, you can do inserts into the result table. Like so,

$ht=@{} # Create empty hashtable
foreach ( $i in $SvrNameList ){
   $r = invoke-sqlcmd -ServerInstance $i -Query "SELECT s=@@ServerName, v=@@Version" # Query server names and versions 
   $ht.Add($r.s, $r.v) # Add name and version into hashtable
}

# Enumerate the hashtable and generate insert commands
$ht.GetEnumerator() | % {
    invoke-sqlcmd -ServerInstance foo -query "insert into t(s, v) values ('" + $_.value + "', '"+  $_.name +"');" 
}

answered May 23, 2017 at 10:41

vonPryz

24.3k10 gold badges58 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cornflake2068 Over a year ago

Worked with a little tweaking. Thanks

Collectives™ on Stack Overflow

Using PowerShell to Run a SQL query then insert the results into a table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related