代码之家 › 专栏 › 技术社区 › DForck42

将多个查询结果返回到单个数据集中

sql-server-2016 powershell sql-server

DForck42 · 技术社区 · 6 年前

我目前有一个powershell进程,它构建查询字符串、创建数据表、创建sql连接、创建sql命令、创建数据读取器,并将sql命令的结果加载到数据表中。然后,我将该数据表作为用户定义的表类型传递给sql server。它看起来像这样:

$Query =
    "
    SELECT 
        group_id AS GroupID,
        listener_id AS ListenerID,
        dns_name AS DNSName,
        [Port],
        is_conformant AS IsConformant,
        ip_configuration_string_from_cluster AS IPConfigurationString
    FROM master.sys.availability_group_listeners
    "

    #where we're storing query results
    $Datatable = New-Object System.Data.DataTable


    $sqlConnection = new-object System.Data.SqlClient.SqlConnection
    $sqlConnection.ConnectionString = "Server=ServerName; Database=Audit;Trusted_Connection=True;Connect Timeout=5;"
    $sqlConnection.Open()


    $sqlCommand = new-object System.Data.SqlClient.SqlCommand
    $sqlCommand.Connection = $sqlConnection
    $sqlCommand.CommandText = $Query
    $sqlCommand.CommandType = [System.Data.CommandType]::Text
    $sqlCommand.CommandTimeout = 5


    $DataReader = $sqlCommand.ExecuteReader()
    $Datatable.Load($DataReader)


    if ($Datatable.Rows.Count -gt 0)
    {
        Write-Host "        Insert Results"


        $sqlConnection = new-object System.Data.SqlClient.SqlConnection
        $sqlConnection.ConnectionString = "Server='servername; Database=Audit;Trusted_Connection=True;"
        $sqlConnection.Open()


        $sqlCommand = new-object System.Data.SqlClient.SqlCommand
        $sqlCommand.Connection = $sqlConnection
        $sqlCommand.CommandText = "InsertResults"
        $sqlCommand.CommandType = [System.Data.CommandType]::StoredProcedure
        $sqlCommand.CommandTimeout = 10
        $sqlCommand.Parameters.Add("@results", [System.Data.SqlDbType]::Structured).value = $datatable


        $sqlCommand.ExecuteNonQuery()                     
        $sqlConnection.Close()    
    }
    else
    {
        Write-Host "        No Results"
    }

我想做的是修改查询字符串,使其返回多个结果,即它将有多个select语句,并将这些结果放入多个数据表中,然后将这些单独的数据表传递给sql server。我知道powershell有一个数据集对象,但我不确定您将如何填充它。

这可能吗?你会怎么做?

1 回复 | 直到 6 年前

DForck42 6 年前

我想出来了,这是可能的。您只需使用SQL适配器即可。

$Query =
"
SELECT 
    group_id AS GroupID,
    listener_id AS ListenerID
FROM master.sys.availability_group_listeners

SELECT 
    g.group_id AS GroupID,
    CAST(g.name AS VARCHAR(255)) AS AGName
FROM master.sys.availability_groups g
"


$sqlConnection = new-object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = "Server=servername; Database=master;Trusted_Connection=True;Connect Timeout=5;"
$sqlConnection.Open()


$sqlCommand = new-object System.Data.SqlClient.SqlCommand
$sqlCommand.Connection = $sqlConnection
$sqlCommand.CommandText = $Query
$sqlCommand.CommandType = [System.Data.CommandType]::Text
$sqlCommand.CommandTimeout = 5


$Dataset = New-Object System.Data.DataSet
$sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$sqlAdapter.SelectCommand = $sqlCommand

[void]$sqlAdapter.Fill($Dataset)


$sqlConnection.Close()


Write-Host $Dataset.Tables[0].Rows[0].data

$dt1 = New-Object System.Data.DataTable
$dt2 = New-Object System.Data.DataTable
$dt1 = $Dataset.Tables[0]
$dt2 = $Dataset.Tables[1]

foreach($row in $dt1)
{
    Write-Host $row.GroupID $row.ListenerID
}


foreach($row in $dt2)
{
    Write-Host $row.GroupID $row.AGName
}