Update SQL table using PowerShell hash table without a foreach loop

Question

MSSQL lets you do a multi-row UPDATE and INSERT from some source, like another table, or JSON & XML data, etc. Psuedo-example:

INSERT INTO TBL SELECT * FROM SOURCE

Is it possible to use a PowerShell hash table as the source?

  #psuedo code

$carHashTable = [pscustomobject]@{

    color = 'red'
    make = 'geo'
    model = 'metro'
    year = 1996
    insured = 0
}


#here-string
$sql = @"

    insert into
        SomeDumbTable
    select
        *
    from $carHashTable

"@

Invoke-Sqlcmd -Query $sql

All the examples I've seen online use a foreach loop to do the actual insert or update. Bossmang says loops (in SQL proper) are bad.

The only other option I can think of is using PowerShell to create a temp table in SQL, then use the temp table as the source to do the the multi-insert.

From a PowerShell prompt have you tried Import-Module SqlServer and Get-Help Write-SqlTableData -Full? — AlwaysLearning
– AlwaysLearning, Commented Apr 13, 2021 at 21:50
Does this answer your question? Powershell Stored Procedure with User-defined type parameter Use a table-valued parameter, which is almost the same as a table variable. Note that that post doesn't mention you also need to supply $param.TypeName — Charlieface
– Charlieface, Commented Apr 13, 2021 at 23:35
The option to write-SqlTableData is AMAZAING; unfortunately though, it does not support #temp tables. I want to do this without using a Stored Procedure; however, it is an option, and that article helps. I even tried SqlBulkCopy but that didn't work, either $bulkCopy.DestinationTableName = "tempdb..#Test187". "Loops are bad" in SQL, but not in PowerShell, I guess? I'm going to have to do a foreach with an update per record. It doesn't seem like PowerShell has a SQL update cmdlet yet, or the option to convert objects into DataTables. — NamedArray
– NamedArray, Commented Apr 14, 2021 at 21:54

NamedArray · Accepted Answer · 2021-04-16 17:35:39Z

Attempting to answer my own question. If I make an incorrect statement, please correct me.

1) YES. You can convert a hash table using this module created by James Brundage into an object that the SqlServer module can deal with.

#import module that converts PSObjects into a DataTable that SQL can handle (external author)
import-module -name C:\PowerShell\ConvertTo-DataTable.ps1 -Verbose

#import the csv, and convert it to a DataTable
$Jobs = import-csv -Path $Repository\$File -Delimiter '|' | ConvertTo-DataTable

2) For the larger question, can you UPDATE without a foreach loop?, I'm going to say NO, at least not with a SQL #temp table, which was my original intention. Here's what I've observed:

Write-SqlTableData does not appear to support #temp tables
Write-SqlTableData DOES allow me to use the converted PS $object to write data; however, it only appends data to tables; I don't know of a way to UPDATE on some key
The only way I was able to achieve this was by gleaning a lot on StackOverflow, and bringing in the SQL .NET client libraries System.Data.SqlClient.
Those libraries allowed me to open and maintain a session with SQL so that I could create a #temp table, bulk copy content into it, then finally UPDATEing the real target table.
The SqlServer Modules for PowerShell seems to immediately open and close the session, so any #temp tables created there are only avilable to that session
The session created by the .NET client libraries can be opened and maintained to run multiple queries, but it's separate from sessions created by the SqlServer modules -- PS can't gain access to #temp tables created in .Net sessions, and vice versa.

Collectives™ on Stack Overflow

Update SQL table using PowerShell hash table without a foreach loop

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related