如何从变量中选择? [英] How to Select from a variable?

问题描述

我用于执行DMV查询的辅助函数:

My helper function for executing a DMV query:

Function DMV_Query($DMV_Query) {
## Prepare the connection string based on information provided
$connectionString = "Provider=msolap;Data Source=$AS_Server;Initial Catalog=$CUBE;"

## Connect to the data source and open
$connection = New-Object System.Data.OleDb.OleDbConnection $connectionString
$command = New-Object System.Data.OleDb.OleDbCommand
$command.Connection = $connection 
$command.CommandText = $DMV_Query 
$connection.Open()

## Fetch the results, and close the connection
$adapter = New-Object System.Data.OleDb.OleDbDataAdapter #$command
$adapter.SelectCommand = $command 
$dataset = New-Object System.Data.DataSet
[void] $adapter.Fill($dataSet)
$connection.Close()

## Return all of the rows from their query
$dataSet.Tables[0]
}

示例通话:

$dmvResult = DMV_Query 'SELECT [SESSION_ID], [SESSION_SPID]
                        FROM $SYSTEM.DISCOVER_SESSIONS'
$dmvResult

存储在$dmvResult中的内容示例:

PS> $dmvResult | ConvertTo-Csv
"SESSION_ID","SESSION_SPID"     
"D0kl8975r6df65","5567"
"fghu5r67f65","2234"

我想从$dmvResult变量中选择所有列，并将它们插入到SQL表中.这是我可以从变量中选择的方式吗?

I want to select all columns from the $dmvResult variable and insert them into a SQL table. Is this the way I can select from a variable?

# Doesn't work.

INSERT INTO [dbo].[Table]
SELECT * FROM @dmvResult

推荐答案

注意:下面的答案可能是在PowerShell中的高级字符串插值中的练习中感兴趣的，但是考虑到$dmvResult包含由这篇文章.

Note: The answer below may be of interest as an exercise in advanced string interpolation in PowerShell, but given that $dmvResult contains the rows of an existing DataTable object returned form a previous query, using a bulk-copy operation is simpler and far more efficient, as discovered by Cataster themselves: see this post.

您似乎正在尝试通过在PowerShell中构造一个包含上一个查询$dmvResult的结果的字符串来创建T-SQL语句.

It looks like you're trying to create a T-SQL statement by constructing a string in PowerShell that incorporates the result of a previous query, $dmvResult.

由于 T-SQL对PowerShell变量一无所知，因此您需要明确地合并所有值，以将其传递给语句 string .

Since T-SQL knows nothing about PowerShell variables, you need to explicitly incorporate all values to pass to your INSERT INTO statement in the statement string.

以下是一个示例，根据您的示例数据，使用PowerShell强大的可扩展字符串 (字符串插值；有关背景，请参见

Here's an example, based on your sample data, using PowerShell's powerful expandable strings (string interpolation; for background, see this answer):

# Sample query result / imported CSV.
$dmvResult =
  [pscustomobject] @{ SESSION_ID = 'D0kl8975r6df65'; SESSION_SPID = 5567 },
  [pscustomobject] @{ SESSION_ID = 'fghu5r67f65'; SESSION_SPID = 2234 }

# Construct the INSERT INTO statement from $dmvResult, using an expandable
# string ("..." with $variable and $(<command> references))
$insertStatement = @"
INSERT INTO [dbo].[Table]
  ($($dmvResult[0].psobject.properties.name -join ', '))
VALUES
  $(
    @(foreach ($obj in $dmvResult) {
      $values = foreach ($value in $obj.psobject.properties.value) {
        if ($value -is [string]) { "`"$value`"" } else { $value }
      }
      '(' + ($values -join ', ') + ')'
    }) -join ",`n  "
  )
"@

# Output the constructed statement string.
$insertStatement

请注意，取决于您的数据，可能需要使属性值的字符串化更加复杂；上面的代码仅区分字符串和非字符串:前者用"..."括起来，后者按原样.

Note that the stringification of the property values may need to be made more sophisticated, depending on your data; the code above distinguishes just between strings and non-strings: the former are stringified with enclosing "...", the latter as-is.

上面的产量:

INSERT INTO [dbo].[Table]
  (SESSION_ID, SESSION_SPID)
VALUES
  ("D0kl8975r6df65", 5567),
  ("fghu5r67f65", 2234)

这篇关于如何从变量中选择?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！