Use Powershell To Combine Datatable And Schema Using Merge

The more often you work with datatables in Powershell the more opportunities appear where you can utilize more features. We will look at the Merge method as well as updating data and adding new columns to an existing datatable.

For this example we will be using the “WideWorldImporters” (WWI) database so everyone can follow along. We will create a new Datatable, load it, copy it and add new fields to the schema.

Create A New Datatable In Powershell

Use the New-Object cmdlet to create a new Datatable. The object name can be anything but we will be using $Datatable here.

$Datatable = New-Object System.Data.DataTable

and when we need to add some columns to it we can use the following:

$Datatable.Columns.Add("Column1") 
$Datatable.Columns.Add("Column2")
$Datatable.Columns.Add("Column3")

Note: If you need to use specific data types for each column, see the previous post on creating a datatable.

Load the Datatable From Our WWI Database

Using the WWI database, only a few fields and the Invoke-SqlCmd cmdlet we can load our Datatable with the SQL results.

$Datatable = New-Object System.Data.DataTable

$query ="SELECT TOP 30 
     [PreferredName]
    ,[EmailAddress]
    ,[ValidFrom] 
FROM [WideWorldImporters].[Application].[People]"

$people = Invoke-Sqlcmd -query $query -ServerInstance VMSERVER99\VMSQL1

$Datatable.Columns.Add("PreferredName") 
$Datatable.Columns.Add("EmailAddress")
$Datatable.Columns.Add("ValidFrom")

 foreach($person in $people) {    

    $row = $Datatable.NewRow()
    
    $row.PreferredName = $person.PreferredName
    $row.EmailAddress = $person.EmailAddress
    $row.ValidFrom = $person.ValidFrom
 
    $Datatable.Rows.Add($Row)
 }
$Datatable | Format-Table 

Copy the Datatable

We are going to copy this original Datatable so we can make some schema changes and merge them back in later. The Datatable object has a method called “Copy” so we don’t need to create a new object as we did before. The schema and data will be copied to our $newTable object.

Use Copy to create a new Datatable and check that the schema and data is intact.

$newTable = $Datatable.Copy()
$newTable                ## check that the data is copied
$newTable.Rows.Count     ## get the rowcount (30)

The record data should be exactly the same as $Datatable and the Row count too. We will now add some new columns and change the record data before merging back.

Add Column To Datatable Copy (Schema)

In our example we are creating the Datatable based on the fields in our SQL query. We now want to add 2 more columns and then update the records so they are different. These 2 fields will default to NULL and a data type of string.

$newTable.Columns.Add("Country")
$newTable.Columns.Add("FavoriteColor")

## Modify the existing records with a "_2" and change the color and Country

foreach ($new in $newTable) {
    $new.BeginEdit()
        $new.PreferredName = $new.PreferredName + "_2"
        $new.Country = "Canada"
        $new.FavoriteColor = "Green"
    $new.EndEdit()

}

$newTable               ## check the results
$newTable.Rows.Count    ## You should still have the same number of records (30)

Combine Schema Using Datatable Merge Method

We will now use the datatable merge method to Merge these changes back to the $Datatable object. We should now see that (db tales com) the record number has doubled, the new fields (schema) are added and updated too.
Since we are Merging the 2 Datatables we now have 60 records instead of 30 and the additional 30 records have a “_2” added the the PreferredName as well as the Country and Color.

$Datatable.Merge($newTable)

"Datatable Rows: " + $Datatable.Rows.Count
$Datatable |Format-Table -Auto

Full Powershell Code Example

## Create a new Datatable Object
$Datatable = New-Object System.Data.DataTable

$query ="SELECT TOP 30 
     [PreferredName]
    ,[EmailAddress]
    ,[ValidFrom] 
FROM [WideWorldImporters].[Application].[People]"

$people = Invoke-Sqlcmd -query $query -ServerInstance VMSERVER99\VMSQL1

$Datatable.Columns.Add("PreferredName") 
$Datatable.Columns.Add("EmailAddress")
$Datatable.Columns.Add("ValidFrom")

 foreach($person in $people) {    

    $row = $Datatable.NewRow()
    
    $row.PreferredName = $person.PreferredName
    $row.EmailAddress = $person.EmailAddress
    $row.ValidFrom = $person.ValidFrom
 
    $Datatable.Rows.Add($Row)
 }

## Make a Copy of the Datatable
$newTable = $Datatable.Copy()

$newTable.Columns.Add("Country")
$newTable.Columns.Add("FavoriteColor")

## Change the data so we can see the difference
foreach ($new in $newTable) {
    $new.BeginEdit()
        $new.PreferredName = $new.PreferredName + "_2"
        $new.Country = "Canada"
        $new.FavoriteColor = "Green"
    $new.EndEdit()

}

## Merge the schema dna data back to the original Datatable
$Datatable.Merge($newTable)

"Datatable Rows: " + $Datatable.Rows.Count
$Datatable |Format-Table -Auto

---