sql - 通过 PowerShell 将包含相同 SQL 查询的 ForEach 循环的结果发送到 2 个单独的变量

Question

在 sql server 中执行此查询 - 它返回 3 行数据。使用写入主机 $1_resultsDataTable 运行脚本并注释掉另一个变量 $2_resultsDataTable - 它只返回数据数组的一行。现在，如果我反转注释以便 $2_resultsDataTable 对写入主机处于活动状态，它会返回 6 行数据。

如何设置它，以便在转储这些变量以查看数据结果时看到分配给 $1_resultsDataTable 和 $2_resultsDataTable 的相同 3 行？

[string] $Server= "SERVER"
[string] $Database = "mvTest"
[string] $UserSqlQuery= $("select m.created_date, m.additional_data as ReasonDown from aeroscout.mv_audit m where m.created_date >= '2020-01-18' and m.additional_data like '%query-text%'")
#

$1_resultsDataTable, $2_resultsDataTable = foreach ($x in 1..2) {
    $resultsDataTable = New-Object System.Data.DataTable
    $resultsDataTable = ExecuteSqlQuery $Server $Database $UserSqlQuery 
    $resultsDataTable # first loop sends output to $1_resultsDataTable, second loop send to $2_resultsDataTable
    Start-Sleep 3
} 

# executes a query and populates the $datatable with the data
function ExecuteSqlQuery ($Server, $Database, $SQLQuery) {
    $Datatable = New-Object System.Data.DataTable

    $Connection = New-Object System.Data.SQLClient.SQLConnection
    $Connection.ConnectionString = "server='$Server';database='$Database';Integrated Security=True;"
    $Connection.Open()
    $Command = New-Object System.Data.SQLClient.SQLCommand
    $Command.Connection = $Connection
    $Command.CommandText = $SQLQuery
    $Reader = $Command.ExecuteReader()
If ($Reader.HasRows) {
   
  while($Reader.Read()) {
        $props = @{}
        for($i = 0; $i -lt $Reader.FieldCount; $i+=1) {
            $name = $Reader.GetName($i)
            $value = $Reader.item($i)
            $props.Add($name, $value)   
        }
        $obj = new-object PSObject -Property $props
        Write-Output $obj
      }
   }
     return $obj
$SqlConnection.Close()


}

#validate we got data
write-host $1_resultsDataTable
Start-Sleep 3
write-host $2_resultsDataTable

标签： sqlpowershell