How to SELECT from a variable and insert into table?

Article
2019-01-31

Question

_{Thursday, January 31, 2019 5:23 PM}

I am returning results from a DMV query into a $variable

how can i then use THAT variable and insert all columns in a sql table?

with @dmvResult, i get this error:

"Must declare the table variable "@dmvResult"."

but i dont want to create another temporary table

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

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

All replies (21)

_{Thursday, January 31, 2019 5:41 PM}

You have not defined an parameters for you command. "@dmvResult" is a variable that is passed.

Also you are calling a function from inside the function. That won't work since the function is not defined when it is created and the call would result I an infinite loop if it succeeded.

Start small and learn how to connect and execute a simple query first then learn how to use parameters in a SQL command.

\(ツ)_/

_{Thursday, January 31, 2019 5:49 PM}

This is how this works in PowerShell.

Function Execute-OleDbQuery{
    Param(
        $Query,
        $Server,
        $Catalog
    )
    
    $connectionString = "Provider=msolap;Data Source=$Server;Initial Catalog=$Catalog;"
    
    $conn = New-Object System.Data.OleDb.OleDbConnection $connectionString
    $conn.Open()
    $cmd = $conn.CreateCommand()
    $cmd.CommandText = $Query
    
    ## Fetch the results, and close the connection
    $rdr = $cmd.ExecuteReader()
    $dt = New-Object System.Data.DataTable
    [void]$dt.Load($rdr)
    $conn.Close()
    
    # return the datatable
    $dt
}

$DMV_Query = "Select-Object [SESSION_ID],[SESSION_SPID] FROM $SYSTEM.DISCOVER_SESSIONS"
Execute-OleDbQuery -Query $DMV_Query -Server $AS_Server -Catalog $Cube

\(ツ)_/

_{Thursday, January 31, 2019 6:09 PM}

This is how this works in PowerShell.

Function Execute-OleDbQuery{
    Param(
        $Query,
        $Server,
        $Catalog
    )
    
    $connectionString = "Provider=msolap;Data Source=$Server;Initial Catalog=$Catalog;"
    
    $conn = New-Object System.Data.OleDb.OleDbConnection $connectionString
    $conn.Open()
    $cmd = $conn.CreateCommand()
    $cmd.CommandText = $Query
    
    ## Fetch the results, and close the connection
    $rdr = $cmd.ExecuteReader()
    $dt = New-Object System.Data.DataTable
    [void]$dt.Load($rdr)
    $conn.Close()
    
    # return the datatable
    $dt
}

$DMV_Query = "Select-Object [SESSION_ID],[SESSION_SPID] FROM $SYSTEM.DISCOVER_SESSIONS"
Execute-OleDbQuery -Query $DMV_Query -Server $AS_Server -Catalog $Cube

\(ツ)_/

i meant thats what i already have....DMV_Query is working fine already. and I can export $dmvResult to csv just fine. All i want to do is be able to export/store/insert into a sql table those same results.

in other words, in your example, how would you do this:

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

_{Thursday, January 31, 2019 6:16 PM}

Then you have to create a command object that accepts a parameter and use "$cmd.ExecuteNonQuery()" to do the insert.

Use my code and modify it to do an insert.

You can also search for examples of how to insert data into tables using PowerShell.

\(ツ)_/

_{Thursday, January 31, 2019 6:19 PM}

Then you have to create a command object that accepts a parameter and use "$cmd.ExecuteNonQuery()" to do the insert.

Use my code and modify it to do an insert.

You can also search for examples of how to insert data into tables using PowerShell.

\(ツ)_/

well the reason i posted here is because I have been researching how to insert all day yesterday and today. posting here is my last resort, as I cannot find any way online of what I am looking for.

_{Thursday, January 31, 2019 6:21 PM}

Here is a quick example:

Function Execute-OleDbInsert{
    Param(
        $Query,
        $ParameterName,
        $ParameterValue,
        $Server,
        $Catalog
    )
    
    $connectionString = "Provider=msolap;Data Source=$Server;Initial Catalog=$Catalog;"
    
    $conn = New-Object System.Data.OleDb.OleDbConnection $connectionString
    $conn.Open()
    $cmd = $conn.CreateCommand()
    $cmd.CommandText = $Query
    [void]$cmd.Parameters.Add($ParameterName,$ParameterValue)
    
    ## Fetch the results, and close the connection
    $cmd.ExecuteNonQuery()
    $dt = New-Object System.Data.DataTable
}
$insertQuery = '< your insert query with one parameter'
Execute-OleDbQuery -Query $insertQuery -ParameterName '@whatever' -ParameterValue $value -Server $AS_Server -Catalog $Cube

\(ツ)_/

_{Thursday, January 31, 2019 6:40 PM}

well the reason i posted here is because I have been researching how to insert all day yesterday and today. posting here is my last resort, as I cannot find any way online of what I am looking for.

You didn't try searching very much. I found it in one second:

https://www.google.com/search?newwindow=1&source=hp&ei=wUBTXKjND5PZjgSQj4r4AQ&q=powershell+insert+into+sql&oq=powershell+insert&gs_l=psy-ab.1.0.0l10.289.10184..12721...3.0..0.171.2447.1j18......0....1..gws-wiz.....0..0i131j0i13.oVu1Tmr0ijo

Read the help for your search engine to learn how to get the search results you want.

\(ツ)_/

_{Thursday, January 31, 2019 6:44 PM}

well the reason i posted here is because I have been researching how to insert all day yesterday and today. posting here is my last resort, as I cannot find any way online of what I am looking for.

You didn't try searching very much. I found it in one second:

https://www.google.com/search?newwindow=1&source=hp&ei=wUBTXKjND5PZjgSQj4r4AQ&q=powershell+insert+into+sql&oq=powershell+insert&gs_l=psy-ab.1.0.0l10.289.10184..12721...3.0..0.171.2447.1j18......0....1..gws-wiz.....0..0i131j0i13.oVu1Tmr0ijo

Read the help for your search engine to learn how to get the search results you want.

\(ツ)_/

none of those results address my main problem: How to insert FROM a VARIABLE

_{Thursday, January 31, 2019 6:49 PM}

You didn't look very hard:

https://davegugg.wordpress.com/2017/09/30/inserting-into-sql-server-from-powershell-using-parameters/

Again - learn how to use search queries and you will find results quickly.

\(ツ)_/

_{Thursday, January 31, 2019 6:57 PM}

You didn't look very hard:

https://davegugg.wordpress.com/2017/09/30/inserting-into-sql-server-from-powershell-using-parameters/

Again - learn how to use search queries and you will find results quickly.

\(ツ)_/

nope. this is showing something completely different. my $dmvResult is not a parameter, its a variable that already exists and holds data

$sqlCmnd.Parameters.ADD((New-OBJECT DATA.SQLClient.SQLParameter("@ServerName",[Data.SQLDBType]::VarChar, 255))) | OUT-NULL
$sqlCmnd.Parameters.ADD((New-OBJECT DATA.SQLClient.SQLParameter("@MemoryUsed",[Data.SQLDBType]::DECIMAL, 5,2))) | OUT-NULL

_{Thursday, January 31, 2019 7:12 PM}

You question was about this:

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

"@dnvResult" is a parameter token. It needs to have a parameter added to the command object and it has to be assigned the value you need. In this case the sql requires either a table name or a view name.

Your like name variable contains a table object which cannot be used in a sql insert.

What you are trying to do doesn't make any sense from the SQL or PowerShell view.

If you are just trying to copy rows from one table to another then just use a query:

"SELECT ... FROM … INSERT INTO …."

This can be executed in an ExecuteNonQuery call and it will copy the rows. No need for data tables.

\(ツ)_/

_{Thursday, January 31, 2019 7:21 PM}

You question was about this:

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

"@dnvResult" is a parameter token. It needs to have a parameter added to the command object and it has to be assigned the value you need. In this case the sql requires either a table name or a view name.

Your like name variable contains a table object which cannot be used in a sql insert.

What you are trying to do doesn't make any sense from the SQL or PowerShell view.

If you are just trying to copy rows from one table to another then just use a query:

"SELECT ... FROM … INSERT INTO …."

This can be executed in an ExecuteNonQuery call and it will copy the rows. No need for data tables.

\(ツ)_/

im glad we are now on the right track. the problem with inserting from one table to another here is the fact that the supposed "first table" doesnt really "exist", because its a DMV, a rowset.

and in order for me to get that such rowset i have to conenct to analysis services server.

This is why im using a variable, because i cannot execute a query like this:

"INSERT INTO [dbo].[Table] SELECT * FROM $SYSTEM.DISCOVER_SESSIONS"

THAT wont work because first of all the selection has to know to select from an SSAS CUBE, and hence my DMV_Query function. and second of all, it has to know where to insert from, i.e. the data must be stored somewhere...which in my case, im storing it in $dmvResult variable.

All i hope to do is insert from $dmvResult. the reason i used @dmvResult is because I thought thats the correct way to call the variable in a SQL statement. I dont intend for parametrization if thats not how it works...

but then again, this doesnt work either:

"INSERT INTO [dbo].[Table] SELECT * FROM $dmvResult"

_{Thursday, January 31, 2019 7:31 PM}

If you have a datatable then you would use SqlBulkCopy to load it into a SQL table.

Note that SQLServer can query an AS server very easily. Post in the SQLServer forum to learn how to write SQL that can do this.

\(ツ)_/

_{Thursday, January 31, 2019 7:51 PM}

Here is all it takes to upload a data table to a server table:

$sqlTable='MyDataTable'
$dataSource='MyServer\MyInstance'
$dataBase='db1'

$ConnectionString ='Data Source={0}; Database={1}; Trusted_Connection=True;' -f $dataSource,$dataBase
$bulkCopy = new-object Data.SqlClient.SqlBulkCopy($ConnectionString)
$bulkCopy.DestinationTableName=$sqlTable
$bulkCopy.WriteToServer($dataTable)

\(ツ)_/

_{Thursday, January 31, 2019 7:57 PM}

If you have a datatable then you would use SqlBulkCopy to load it into a SQL table.

Note that SQLServer can query an AS server very easily. Post in the SQLServer forum to learn how to write SQL that can do this.

\(ツ)_/

actually i tried querying AS server the very first thing through SQL. it returned that it couldnt connect to the server.

thus, the only way i was able to connect was using that function i have posted in this thread, i.e. DMV_Query that i wrote

_{Thursday, January 31, 2019 8:28 PM}

Here is all it takes to upload a data table to a server table:

$sqlTable='MyDataTable'
$dataSource='MyServer\MyInstance'
$dataBase='db1'

$ConnectionString ='Data Source={0}; Database={1}; Trusted_Connection=True;' -f $dataSource,$dataBase
$bulkCopy = new-object Data.SqlClient.SqlBulkCopy($ConnectionString)
$bulkCopy.DestinationTableName=$sqlTable
$bulkCopy.WriteToServer($dataTable)

\(ツ)_/

EXCELLENT! FINALLY! BULKCOPY is what im looking for!!

see? thats all i wanted, copy the values from $dmvResult (which is a datatable) into a sql Table.

Now, I have one issue: the bulkCopy is copying the columns by position. for example,

let say my SQL Table has these columns: column A, B, and C

what $dmvResult (the dataTable) holds is: column B and C for example from the DMV Query

It is for some reason copying column B into column A, and column C intol column B.

can i specify to bulkCopy to only copy into MATCHING columns? so that column B would copy to column B as it should, and C into C....etc

for now, its simply copying by position, which is not right

_{Thursday, January 31, 2019 8:54 PM}

You have to convert the column types wither by ding adding a column spec or by converting them in the initial query.

Rad up on the command: /en-us/dotnet/api/system.data.sqlclient.sqlbulkcopy.columnmappings?view=netframework-4.7.2

\(ツ)_/

_{Thursday, January 31, 2019 8:58 PM}

You have to convert the column types wither by ding adding a column spec or by converting them in the initial query.

Rad up on the command: /en-us/dotnet/api/system.data.sqlclient.sqlbulkcopy.columnmappings?view=netframework-4.7.2

\(ツ)_/

ya i found out i have to explicitly mention the columns $bulkCopy.ColumnMappings.Add($col1, $col2)

shame, i was hoping there was an automatic mapping option :/

_{Thursday, January 31, 2019 10:38 PM}

ya i found out i have to explicitly mention the columns $bulkCopy.ColumnMappings.Add($col1, $col2)

shame, i was hoping there was an automatic mapping option :/

You can change the column names and data types in the initial query. Just make it return what matches the target table anit will bee automatic.

\(ツ)_/

_{Thursday, January 31, 2019 10:43 PM}

ya i found out i have to explicitly mention the columns $bulkCopy.ColumnMappings.Add($col1, $col2)

shame, i was hoping there was an automatic mapping option :/

You can change the column names and data types in the initial query. Just make it return what matches the target table anit will bee automatic.

\(ツ)_/

which intital query?

_{Thursday, January 31, 2019 10:46 PM}

The ELECT statement that creates the DataTable. If you use my correct query method ll of this would be much easier.

"DataAdapters" are only used for forms and with a full set of CRUD operations. You are copying old code that is not the way to go in PowerShell.

\(ツ)_/

Share via

How to SELECT from a variable and insert into table?

Question

All replies (21)

Additional resources