Merge-DataTableToSQL.ps1

#Merge the datatable into SQL
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]$ServerInstance,
        [Parameter(Mandatory=$true)][string]$Database,
        [Parameter(Mandatory=$true)][string]$Target,
        [Parameter(Mandatory=$true)][System.Data.DataTable]$Source,
        [String]$Filter="*",
        [parameter()][System.Management.Automation.PSCredential]$Cred
    )

    Begin {
        #Create connection object to SQL instance
        $SQLConnection = New-Object System.Data.SqlClient.SqlConnection
        $SQLConnection.ConnectionString = "Server = $ServerInstance;Database=$Database;User ID=$($cred.UserName);Password=$($cred.GetNetworkCredential().password);"
        $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 ","
#Do not align this text!
$SQLQuery = @"
MERGE INTO $($Target) AS Target
USING $($Target)_TEMP AS Source
ON Target.[$($PrimaryKey)] = Source.[$($PrimaryKey)]
WHEN NOT MATCHED THEN
    INSERT ($InsertColumns) VALUES ($InsertValues)
WHEN NOT MATCHED BY Source THEN
    DELETE;
"@
                        $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()
        }
    }
}