If you have data that you’re collecting with PowerShell and wan’t to insert new entries or update existing ones in a Database then a SQL MERGE function is what you need. I frequently run scheduled queries to collect information that I want to submit to SQL for reporting purposes. When testing this function I was able to merge a DataTable with 100,000+ rows and 20+ columns very quickly. A unique identifier in the results is used as a primary key in the merger. When there’s a match it updates that row, if it doesn’t exist already it inserts it as a new row. To do this effectively a temporary table is created by first looking up the primary key in the targeted table (if there’s one); then builds the query with all the columns in the source. It also compares the source columns to the target table columns to make sure they correspond for the column mapping. The DataTable is then bulk copied to the temporary table, this becomes the source of the merge to the target table. After which the temporary table is dropped.

Click here to download Merge-DataTableToSQL

Like some of my other SQL functions the source object needs to be a DataTable. You can use the ConverTo-DataTable function to convert any object to a DataTable.

EXAMPLE:

Import-Module C:\PowerShell\ConvertTo-DataTable.ps1
Import-Module C:\PowerShell\Merge-DataTableToSQL.ps1

$Users = Get-ADUser -Server fitchaddc02 -Filter * #Using as example data
$DataTable = ConvertTo-DataTable -Source $Users -NotMatch "write|propert|object|psshow" #Filtering junk columns from Get-ADUser that I don't want
$DataTable.Columns.columnname #This lists the names of all columns
$DataTable.rows[0] #Test to show one row
$DataTable.rows.Count #Number of rows in DataTalbe, can compare to $Users.count

Merge-DataTableToSQL -SQLServer fitchmsql01 -SQLDB DEV -Target dbo.adusers -Source $DataTable

CODE:

function Merge-DataTableToSQL
{
<#
 .EXAMPLE
 Merge-DataTableToSQL -SQLServer fitchmsql01 -SQLDB dev -Target dbo.adusers -Source $DataTable
 .PARAMETER Source
 A DataTable that will be merged witht eh target tabled.
 .PARAMETER SQLServer
 Named instances SERVERNAME\INSTANCENAME | If it's a default instance only use server name, do not add \MSSQLSERVER.
 .PARAMETER SQLDB
 Name of the database where the target table is.
 .PARAMETER Target
 The Table in which you're targeting
 .PARAMETER Filter
 Default="*" Used in selecting the columns from the target.
 .PARAMETER Security
 Default="SSPI" Used in connection string to SQL DB. User account running script should have permission to create and drop tables.
 .PARAMETER Timeout
 Default="60" Used in connection string.
#>
 [CmdletBinding()]
param(
 [Parameter(Mandatory=$true)][string]$SQLServer,
 [Parameter(Mandatory=$true)][string]$SQLDB,
 [Parameter(Mandatory=$true)][string]$Target,
 [Parameter(Mandatory=$true)][System.Data.DataTable]$Source,
 [String]$Filter="*",
 [String]$Security="SSPI",
 [Int]$Timeout="60"
)

Begin {
 #Create connection object to SQL instance
 $SQLConnection = New-Object System.Data.SqlClient.SqlConnection
 $SQLConnection.ConnectionString = "Server=$SQLServer;Database=$SQLDB;Integrated Security=$Security;Connection Timeout=$Timeout"
 $SQLConnection.Open()
}
Process{
 if ($SQLConnection.State -ne [Data.ConnectionState]::Open){"Connection to SQL DB not open"}
 else{
 #Get columns for table in SQL and compare to column in source DataTable
 $SQLCommand = New-Object System.Data.SqlClient.SqlCommand
 $SQLCommand.Connection = $SQLConnection
 $SQLCommand.CommandText = "SELECT $($Filter) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_Name = '$(($Target.Split(".") | Select -Index 1))'"
 $SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
 $SQLAdapter.SelectCommand = $SQLCommand
 $SQLColumns = New-Object System.Data.DataTable
 $SQLAdapter.Fill($SQLColumns) | Out-Null
 $Columns = $SQLColumns.COLUMN_NAME
 if ($Compare = ((Compare-Object $SQLColumns.COLUMN_NAME $Source.Columns.ColumnName -PassThru) -join ", ")){
 "DataTable and SQL table contain different columns: $Compare"
 }
 else{

 #What is the primary key of the target table
 $PrimaryKey = New-Object System.Data.DataTable
 $SQLCommand.CommandText = "SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_Name = '$($Target.Split(".") | Select -Index 1)' AND CONSTRAINT_NAME LIKE 'PK_%'"
 $SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
 $SQLAdapter.SelectCommand = $SQLCommand
 $SQLAdapter.Fill($PrimaryKey) | Out-Null
 $PrimaryKey = $PrimaryKey | Where-Object {$_.CONSTRAINT_NAME -like 'PK_*'} | Select -ExpandProperty COLUMN_NAME -First 1
 if($PrimaryKey -eq $null){"SQL Table does not have primary key"
 }
 else{
 #Create temporary table for bulk insert
 $CreateColumns = ($CreateColumns = foreach($Column in ($Columns | Where-Object {$_ -ne $PrimaryKey})){"["+$Column+"] [nvarchar] (max) NULL"}) -join ","
 $SQLQuery = "CREATE TABLE $($Target)_TEMP([$($PrimaryKey)] [nvarchar](255) NOT NULL PRIMARY KEY, $CreateColumns)"
 $SQLCommand.CommandText = $SQLQuery
 $Results = $SQLCommand.ExecuteNonQuery()
 if ($Results -ne -1){"Unable to create temp table $($Target)_TEMP"}
 else{

 #Bulk insert source DataTable into temporary SQL table
 $SQLBulkCopy = New-Object ("System.Data.SqlClient.SqlBulkCopy") $SQLConnection
 $SQLBulkCopy.DestinationTableName = "$($Target)_TEMP"
 $SQLBulkCopy.BatchSize = 5000
 $SQLBulkCopy.BulkCopyTimeout = 0
 foreach ($Column in $Columns){[void]$SQLBulkCopy.ColumnMappings.Add($Column, $Column)}
 $SQLBulkCopy.WriteToServer($Source)

 #Build and execute SQL merge command
 $Updates = (($Updates = foreach ($Column in $Columns -ne $PrimaryKey)
 {
 "Target.[$($Column)]"+" = "+("Source.[$($Column)]")
 }) -join ",")
 $InsertColumns = ($InsertColumns = foreach ($Column in $Columns){"[$Column]"}) -join ","
 $InsertValues = ($InsertValues = foreach ($Column in $Columns){"Source.[$Column]"}) -join ","
$SQLQuery = @"
MERGE INTO $($Target) AS Target
USING $($Target)_TEMP AS Source
ON Target.[$($PrimaryKey)] = Source.[$($PrimaryKey)]
WHEN MATCHED THEN
 UPDATE SET $Updates
WHEN NOT MATCHED THEN
 INSERT ($InsertColumns) VALUES ($InsertValues);
"@
 $SQLCommand.CommandText = $SQLQuery
 try{
 $Results = $SQLCommand.ExecuteNonQuery()
 }
 catch{
 Write-Host "Exception with SQL Command: MERGE INTO $Target USING $($Target)_TEMP | Can be caused by incorrect Data Types"
 }
 "$Results rows affected"
 #Drop temporary table
 $SQLCommand.CommandText = "DROP TABLE $($Target)_TEMP"
 $Results = $SQLCommand.ExecuteNonQuery()
 if($Results -ne -1){
 "Unable to DROP TABLE $($Target)_TEMP"
 }
 #End of create temporary table
 }
 #End of require primary key
 }
 #End of compare DataTable and SQL Table columns
 }
 }
}
end{
 if ($SQLConnection.State -eq [Data.ConnectionState]::Open){
 $SQLConnection.Close()
 }
}
}