PowerShell Arrays and Hash Tables–#SQLNewblogger


I was watching the GroupBy talk the other day and noticed that Cláudio Silva was using arrays, or what appeared to be arrays, in his talk. That was an interesting technique, one that I haven’t used very much.

A day later, I ran into an explanation on dbatools.io, that showed this code:

PS C:> $columns = @{
>> Text = 'FirstName'
>> Number = 'PhoneNumber' 
>> }

That didn’t quite seem like what I wanted, so I decided to investigate more.

I looked up PowerShell Arrays, and that wasn’t what I wanted. These are a list of values, as in

$a = 1, 2,3

Which gives me this:


Useful, but not for my purposes. I need to map things together, which means a hash table.

Hash Tables

It turns out I need a hash table. This is a key value pair that lets me pick a name and value and store them together. The way I construct these are with the @{} structure. Inside here, I set semi-colon separated pairs, with the name=value syntax.

Here’s an example I used:

$ColList = @{Date="EventDate"; Event="Event"}

In here I map two keys (Date and Event) to two values (EventDate and Event). For the cmdlet I am using, this allows me to map these two columns together. When I need a value, I can use the $variable.key to get the value back.

2020-10-28 13_37_45-C__Users_Steve

I assume this is what the SqlBulkCopy cmdlet uses, which is what dbatools wraps. I ended up passing this $ColList hash table in for the –ColumnMap parameter.


A quick writeup that I used to solve a problem. I had some issues figuring this out, and some searching and experimenting got me a little better understanding of what was happening.

After about 30 minutes of some work, I took 10 minutes to type this up and explain it to myself. A good example of what you could add to your blog, showing how you use this in your work.

Original post (opens in new tab)
View comments in original post (opens in new tab)