How to match columns from another query to insert values into matching columns

PowerShell Help
1

I have a DMV query that generates database/cube metadata.

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

I want to take these resulted columns’ data [SESSION_ID],[SESSION_SPID] and have an insert query automatically recognize matching columns in an empty table, let say called “DMV_Table”.

I essentially want to accomplish something like this:

'INSERT INTO DMV_Table (SESSION_ID, SESSION_SPID) VALUES ($DMV_QUERY)'

in which the values are automatically inserted into the matched column name

How can i do that?

2
3

[quote quote=136953]https://www.w3schools.com/sql/sql_insert_into_select.asp

[/quote]

That doesnt contain any use case anything related to my scenario. I want to be able to insert into a table using results from PREVIOUS query, something like this

'INSERT INTO DMV_Table (SESSION_ID, SESSION_SPID) VALUES ($DMV_QUERY)'
4

Are you looking for MSAS query help or PowerShell help?

MSAS query you can frame it like this…

[pre]DMV_Query = “INSERT INTO DMV_Table (‘SESSION_ID’, ‘SESSION_SPID’) SELECT [SESSION_ID],[SESSION_SPID] FROM $SYSTEM.DISCOVER_SESSIONS”[/pre]

If you want to execute the same in PowerShell, use Invoke-ASCmd from sqlserver module.

5

[quote quote=137019]Are you looking for MSAS query help or PowerShell help?

MSAS query you can frame it like this…

<textarea class="ace_text-input" style="opacity: 0; height: 17.9048px; width: 7.20119px; left: -728px; top: 0px;" spellcheck="false" wrap="off"></textarea>
1
DMV_Query = "INSERT INTO DMV_Table ('SESSION_ID', 'SESSION_SPID') SELECT [SESSION_ID],[SESSION_SPID] FROM $SYSTEM.DISCOVER_SESSIONS"
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
If you want to execute the same in PowerShell, use Invoke-ASCmd from sqlserver module.

[/quote]
Invokeascmd doesnt work, I tried it and I am unable to connect to server through it. Due to time crunch i cant spend time diagnosing why thts the case because from past experience it took me a month to figure out a similar issue.

I found out there is an oledb connection using msolap provider, and that allowed me to fetch DMV results nicely. Now those results are stored in $DMV variable and I just want to insert those stored columns/results into a sql table.

Basically something like” insert into table from stored results in $dmv”