How to Select from a variable?

Question

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]
}

Sample call:

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

Sample of the content stored in $dmvResult:

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

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

mklement0 · Accepted Answer · 2019-02-01T03:34:57.977

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.

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.

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.

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.

The above yields:

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

score 0 · Answer 2 · answered Jan 31 '19 at 17:24

0

Use the query like below,Dont store the value in variable..

INSERT INTO [dbo].[Table]
SELECT [SESSION_ID]
      ,[SESSION_SPID]`enter code here`
FROM $SYSTEM.DISCOVER_SESSIONS

answered Jan 31 '19 at 17:24

George

43
1
10

the reason im stroing is because 1. thats a DMV query that executes on SSAS cube object which requires an SSAS connection and 2. because i am initially exporting the data to csv file before inserting, so i want to execute this query separately anayways for this other purpose (csv)...i didnt include csv portion because its irrelevant to main question and didnt want to make thread too detailed. however, i think i have found a way...maybe i have to use dynamic query to declare $dmvResult first? – Cataster Jan 31 '19 at 17:28
Use Temp table to store the value and insert into table..you cant use variable to store multiple column – George Jan 31 '19 at 17:31
refer -https://stackoverflow.com/questions/5120817/how-do-i-store-the-select-column-in-a-variable – George Jan 31 '19 at 17:32
but if thats the case, then how come $dmvResult | Export-Csv is able to export more than one row/column if $dmvResult is holding just one value? i thought it was storing the entire DMV table result because i can see the csv exported file have an entire table structure full of data – Cataster Jan 31 '19 at 17:37
@pratheshp: Your answer misses that there's _PowerShell_ involved in the question: the initial query's results are stored _in a PowerShell variable_, and the OP then wants to use these results _from PowerShell_ in a subsequent SQL operation. – mklement0 Feb 01 '19 at 03:04

score 0 · Answer 3 · answered Jan 31 '19 at 21:11

0

I found a solution, BulkCopy!

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

$ConnectionString ='Data Source=$server; Database=$database; Trusted_Connection=True;'
$bulkCopy = new-object Data.SqlClient.SqlBulkCopy($ConnectionString)
$bulkCopy.DestinationTableName=$Table
$bulkCopy.WriteToServer($dmvResult)

This however does not map the columns correctly if they are not in order, as BulkCopy copies by position, not by name match

answered Jan 31 '19 at 21:11

Cataster

3,081
5
32
79

Given that this is not a full answer and that you've since solved the implied question here as evidenced by https://stackoverflow.com/q/54471629/45375 (in the context of a follow-up question), which my answer now links to, please delete this post. – mklement0 Feb 01 '19 at 03:13

How to Select from a variable?

3 Answers3

Linked