How to add data from SQL server to hashtable using PowerShell?

Question

Basically, I want the data to show up in an excel file like it shows in the SQL database. This is much more simplified version of the work that I need to do but in essence this what it is.

I retrieve the data from SQL and for each item retrieved(which is the primary key) I want the data corresponding to it to be added in the hashtable. I then export this hashtable as a CSV

The CSV file is generated but with some weird data

I am not sure what exactly is wrong because when I Write-host $hashObject I can see the data is in there.

Code

$server = "DESKTOP\SQLEXPRESS"
$database = "AdventureWorks2019"
$hashTable = @{}
$hashObject = @([PSCustomObject]$hashTable)
$query = "SELECT[DepartmentID] FROM [AdventureWorks2019].[HumanResources].[Department]"
$invokeSql = Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query
$departmentResult = $invokeSql.DepartmentID

ForEach($department in $departmentResult){
    $queryAll = "SELECT [Name],[GroupName],[ModifiedDate]FROM [AdventureWorks2019].[HumanResources].[Department] Where DepartmentID=$department"
    $invokeSql = Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $queryAll
    $name = $invokeSql.Name
    $groupName = $invokeSql.GroupName
    $modifiedDate = $invokeSql.ModifiedDate
    $hashObject+=("Department",$department, "Name",$name,"GroupName",$groupName,"ModifiedDate",$modifiedDate)
}
ConvertTo-Csv $hashObject| Export-Csv -Path "C:\Users\Desktop\PowerShell\HashTable_OutputFiles\HashOutput.csv"

At first glance you could do an inner join on your sql query of the 2 tables and save yourself the overhead — Santiago Squarzon, Jun 12 '22 at 00:52
@SantiagoSquarzon there is only one table involved here`[HumanResources].[Department]` the columns are: `[DepartmentID],[Name],[GroupName],[ModifiedDate]` — Brute, Jun 12 '22 at 00:57
Why is the first query needed? If you're querying the same table, `DepartmentID=$department` is not clear — Santiago Squarzon, Jun 12 '22 at 01:01
@SantiagoSquarzon This is much simpliefied version of the real problem. In the real problem the data from the first query is matched against something else and if it is true only then I need the rest of the data. Also, I need the primary key to get data from other tables too(that is after the condition is true). — Brute, Jun 12 '22 at 01:13

Santiago Squarzon · Accepted Answer · 2022-06-12T02:14:24.757

This is a simplified version of what you're attempting to do, in this case you should be able to use the SQL IN Operator in your second query instead of querying your Database on each loop iteration.

As aside, is unclear what you wanted to do when declaring a hash table to then convert it to a PSCustomObject instance and then wrap it in an array:

$hashTable  = @{}
$hashObject = @([PSCustomObject] $hashTable)

It's also worth noting that ConvertTo-Csv and Import-Csv are coded in such a way that they are intended to receive objects from the pipeline. This answer might help clarifying the Why. It's also unclear why are you attempting to first convert the objects to Csv and then exporting them when Import-Csv can (and in this case, must) receive the objects, convert them to a Csv string and then export them to a file.

$server     = "DESKTOP\SQLEXPRESS"
$database   = "AdventureWorks2019"
$query      = "SELECT [DepartmentID] FROM [AdventureWorks2019].[HumanResources].[Department]"
$invokeSql  = Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query
$department = "'{0}'" -f ($invokeSql.DepartmentID -join "','")
$query      = @"
SELECT [Name],
       [GroupName],
       [ModifiedDate]
FROM [AdventureWorks2019].[HumanResources].[Department]
WHERE DepartmentID IN ($department);
"@

Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query |
    Export-Csv -Path "C:\Users\Desktop\PowerShell\HashTable_OutputFiles\HashOutput.csv"

If you want to query the database per ID from the first query, you could do it this way (note this is similar to what you where looking to accomplish, merge the ID with the second results from the second query):

$invokeSql = Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query
$query     = @"
SELECT [Name],
       [GroupName],
       [ModifiedDate]
FROM [AdventureWorks2019].[HumanResources].[Department]
WHERE DepartmentID = '{0}';
"@

& {
    foreach($id in $invokeSql.DepartmentID) {
        $queryID = $query -f $id
        Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $queryID |
            Select-Object @{ N='DepartmentID'; E={ $id }}, *
    }
} | Export-Csv -Path "C:\Users\Desktop\PowerShell\HashTable_OutputFiles\HashOutput.csv"

Thanks for answering. The above code returned only one row. Moving the `$query` in a loop as in `ForEach($item in $department){}` rectified the issue. But I don't understand is that where is the data from the For loop is being stored because it does show up correctly in the CSV file. Secondly, as great this answer is what I really wanted was to add values in an array\hastable, as they were returned,under certain columns, then exporting all that data to CSV file — Brute, Jun 12 '22 at 02:02
@Brute I have added a second example which is more similar to what you where doing before, that one merges the ID with the second query and exports it directly to Csv. — Santiago Squarzon, Jun 12 '22 at 02:15

How to add data from SQL server to hashtable using PowerShell?

1 Answers1