functions/Write-DbcTable.ps1

<#
.SYNOPSIS
Writes the result of Invoke-DbcCheck (with -PassThru) after Convert-DbcResult to a database table

.DESCRIPTION
After running Invoke-DbcCheck (With PassThru) and converting it to a datatable with Convert-DbcResult, this command
will write the results to a database table and will also write the current Checks to another table called dbachecksChecks

.PARAMETER SqlInstance
The Instance for the results

.PARAMETER SqlCredential
The SQL Credential for the instance if required

.PARAMETER Database
The database to write the results

.PARAMETER InputObject
The datatable from Convert-DbcResult

.PARAMETER Table
The name of the table for the results - will be created if it doesn't exist. By default it will be named CheckResults

.PARAMETER Schema
The schema for the table - defaults to dbo

.PARAMETER Truncate
Will truncate the existing table (if results go to a staging table for example)


.EXAMPLE
Invoke-DbcCheck -SqlInstance SQL2017N5 -Check AutoClose -Passthru | Convert-DbcResult -Label Beard-Check | Write-DbcTable -SqlInstance DbaInstance -Database dbadatabase

Runs the AutoClose check against SQL2017N5 and converts to a datatable with a label of Beard-Check and writes it to the dbadatabase on DbaInstance

.EXAMPLE
Import-DbcConfig -Path \\share\ProdMorningChecks.json
Invoke-DbcCheck -SqlInstance $ProdSqlInstances -Passthru | Convert-DbcResult -Label Prod-Morning-Check | Write-DbcTable -SqlInstance DbaInstance -Database dbadatabase

Loads the Prod Morning Checks Configuration and runs the checks against the SQL Instances converts to a datatable with a label of Prod-Morning-Check and writes it to the dbadatabase on DbaInstance

.NOTES
Initial - RMS 28/12/2019
#>

function Write-DbcTable {
    [CmdletBinding(SupportsShouldProcess)]
    [OutputType([string])]
    Param(
        [Parameter(Mandatory)]
        [ValidateNotNull()]
        [string]$SqlInstance,
        [ValidateNotNull()]
        [PSCredential]$SqlCredential,
        [object]$Database,
        [Parameter(Mandatory, ValueFromPipeline)]
        # The pester results object
        [ValidateNotNull()]
        [object]$InputObject,
        [ValidateNotNullOrEmpty()]
        [string]$Table = 'CheckResults',
        [ValidateNotNullOrEmpty()]
        [string]$Schema = 'dbo',
        [switch]$Truncate

    )
    Write-PSFMessage 'Testing we have a Test Results object' -Level Verbose
    if (-not $InputObject) {
        Write-PSFMessage "Uh-Oh - I'm really sorry - We don't have a Test Results Object" -Level Significant
        Write-PSFMessage 'Did You forget the -PassThru parameter on Invoke-DbcCheck?' -Level Warning
        Return ''
    }
    Write-PSFMessage "Connecting to $SqlInstance" -Level Verbose
    $SqlInstanceSmo = Connect-DbaInstance -SqlInstance $SqlInstance -SqlCredential $SqlCredential

    Write-PSFMessage "Checking for dbachecks table in $Database" -Level Verbose
    if (Get-DbaDbTable -SqlInstance $SqlInstanceSmo -Database $Database -Table dbachecksChecks) {
        if ($PSCmdlet.ShouldProcess("$schema.$database" , "On $SqlInstance - Update the dbachecksChecks tables ")) {
            Get-DbcCheck | Write-DbaDbTableData -SqlInstance $SqlInstanceSmo -Database $Database -Table dbachecksChecks -Schema $Schema -AutoCreateTable -Truncate
        }
    } else {
        if ($PSCmdlet.ShouldProcess("$schema.$database" , "On $SqlInstance - Add the dbachecksChecks tables ")) {
            Get-DbcCheck | Write-DbaDbTableData -SqlInstance $SqlInstanceSmo -Database $Database -Table dbachecksChecks -Schema $Schema -AutoCreateTable
        }
    }
    Write-PSFMessage "Checking for $Table in $Database" -Level Verbose
    if (Get-DbaDbTable -SqlInstance $SqlInstanceSmo -Database $Database -Table $Table) {
        Write-PSFMessage "We have $table already - moving on." -Level Verbose
    } else {
        if ($PSCmdlet.ShouldProcess("$schema.$database" , "Create a new table called $table ")) {
            # If specified table does not exists, create with specific datatypes to avoid nvarchar(max)
            $sqlTableCreation = @"
 CREATE TABLE [$schema].[$table](
     [Date] [datetime2](7) NOT NULL,
     [Label] [nvarchar](255) NULL,
     [Describe] [nvarchar](255) NULL,
     [Context] [nvarchar](255) NULL,
     [Name] [nvarchar](600) NULL,
     [Database] [nvarchar](255) NULL,
     [ComputerName] [nvarchar](255) NULL,
     [Instance] [nvarchar](255) NULL,
     [Result] [nvarchar](10) NULL,
     [FailureMessage] [nvarchar](MAX) NULL
 ) ON [PRIMARY]
 GO

 CREATE CLUSTERED INDEX CI_DATE ON [$schema].[$table]
 (
     [Date]
 ) WITH (DATA_COMPRESSION = PAGE) ON [PRIMARY]
 GO
"@

            Invoke-DbaQuery -SqlInstance $SqlInstanceSmo -Database $Database -Query $sqlTableCreation
        }
    }
    if ($PSCmdlet.ShouldProcess("$Schema.$database" , "On $SqlInstance - Add dbachecks results to $Table ")) {
        $InputObject | Write-DbaDbTableData -SqlInstance $SqlInstanceSmo -Database $Database -Table $Table -Schema $Schema -AutoCreateTable -Truncate:$Truncate
    }
}
# SIG # Begin signature block
# MIINEAYJKoZIhvcNAQcCoIINATCCDP0CAQExCzAJBgUrDgMCGgUAMGkGCisGAQQB
# gjcCAQSgWzBZMDQGCisGAQQBgjcCAR4wJgIDAQAABBAfzDtgWUsITrck0sYpfvNR
# AgEAAgEAAgEAAgEAAgEAMCEwCQYFKw4DAhoFAAQUegYAffTRCI+hKr04nGgnT1lL
# /aSgggpSMIIFGjCCBAKgAwIBAgIQAsF1KHTVwoQxhSrYoGRpyjANBgkqhkiG9w0B
# AQsFADByMQswCQYDVQQGEwJVUzEVMBMGA1UEChMMRGlnaUNlcnQgSW5jMRkwFwYD
# VQQLExB3d3cuZGlnaWNlcnQuY29tMTEwLwYDVQQDEyhEaWdpQ2VydCBTSEEyIEFz
# c3VyZWQgSUQgQ29kZSBTaWduaW5nIENBMB4XDTE3MDUwOTAwMDAwMFoXDTIwMDUx
# MzEyMDAwMFowVzELMAkGA1UEBhMCVVMxETAPBgNVBAgTCFZpcmdpbmlhMQ8wDQYD
# VQQHEwZWaWVubmExETAPBgNVBAoTCGRiYXRvb2xzMREwDwYDVQQDEwhkYmF0b29s
# czCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAI8ng7JxnekL0AO4qQgt
# Kr6p3q3SNOPh+SUZH+SyY8EA2I3wR7BMoT7rnZNolTwGjUXn7bRC6vISWg16N202
# 1RBWdTGW2rVPBVLF4HA46jle4hcpEVquXdj3yGYa99ko1w2FOWzLjKvtLqj4tzOh
# K7wa/Gbmv0Si/FU6oOmctzYMI0QXtEG7lR1HsJT5kywwmgcjyuiN28iBIhT6man0
# Ib6xKDv40PblKq5c9AFVldXUGVeBJbLhcEAA1nSPSLGdc7j4J2SulGISYY7ocuX3
# tkv01te72Mv2KkqqpfkLEAQjXgtM0hlgwuc8/A4if+I0YtboCMkVQuwBpbR9/6ys
# Z+sCAwEAAaOCAcUwggHBMB8GA1UdIwQYMBaAFFrEuXsqCqOl6nEDwGD5LfZldQ5Y
# MB0GA1UdDgQWBBRcxSkFqeA3vvHU0aq2mVpFRSOdmjAOBgNVHQ8BAf8EBAMCB4Aw
# EwYDVR0lBAwwCgYIKwYBBQUHAwMwdwYDVR0fBHAwbjA1oDOgMYYvaHR0cDovL2Ny
# bDMuZGlnaWNlcnQuY29tL3NoYTItYXNzdXJlZC1jcy1nMS5jcmwwNaAzoDGGL2h0
# dHA6Ly9jcmw0LmRpZ2ljZXJ0LmNvbS9zaGEyLWFzc3VyZWQtY3MtZzEuY3JsMEwG
# A1UdIARFMEMwNwYJYIZIAYb9bAMBMCowKAYIKwYBBQUHAgEWHGh0dHBzOi8vd3d3
# LmRpZ2ljZXJ0LmNvbS9DUFMwCAYGZ4EMAQQBMIGEBggrBgEFBQcBAQR4MHYwJAYI
# KwYBBQUHMAGGGGh0dHA6Ly9vY3NwLmRpZ2ljZXJ0LmNvbTBOBggrBgEFBQcwAoZC
# aHR0cDovL2NhY2VydHMuZGlnaWNlcnQuY29tL0RpZ2lDZXJ0U0hBMkFzc3VyZWRJ
# RENvZGVTaWduaW5nQ0EuY3J0MAwGA1UdEwEB/wQCMAAwDQYJKoZIhvcNAQELBQAD
# ggEBANuBGTbzCRhgG0Th09J0m/qDqohWMx6ZOFKhMoKl8f/l6IwyDrkG48JBkWOA
# QYXNAzvp3Ro7aGCNJKRAOcIjNKYef/PFRfFQvMe07nQIj78G8x0q44ZpOVCp9uVj
# sLmIvsmF1dcYhOWs9BOG/Zp9augJUtlYpo4JW+iuZHCqjhKzIc74rEEiZd0hSm8M
# asshvBUSB9e8do/7RhaKezvlciDaFBQvg5s0fICsEhULBRhoyVOiUKUcemprPiTD
# xh3buBLuN0bBayjWmOMlkG1Z6i8DUvWlPGz9jiBT3ONBqxXfghXLL6n8PhfppBhn
# daPQO8+SqF5rqrlyBPmRRaTz2GQwggUwMIIEGKADAgECAhAECRgbX9W7ZnVTQ7Vv
# lVAIMA0GCSqGSIb3DQEBCwUAMGUxCzAJBgNVBAYTAlVTMRUwEwYDVQQKEwxEaWdp
# Q2VydCBJbmMxGTAXBgNVBAsTEHd3dy5kaWdpY2VydC5jb20xJDAiBgNVBAMTG0Rp
# Z2lDZXJ0IEFzc3VyZWQgSUQgUm9vdCBDQTAeFw0xMzEwMjIxMjAwMDBaFw0yODEw
# MjIxMjAwMDBaMHIxCzAJBgNVBAYTAlVTMRUwEwYDVQQKEwxEaWdpQ2VydCBJbmMx
# GTAXBgNVBAsTEHd3dy5kaWdpY2VydC5jb20xMTAvBgNVBAMTKERpZ2lDZXJ0IFNI
# QTIgQXNzdXJlZCBJRCBDb2RlIFNpZ25pbmcgQ0EwggEiMA0GCSqGSIb3DQEBAQUA
# A4IBDwAwggEKAoIBAQD407Mcfw4Rr2d3B9MLMUkZz9D7RZmxOttE9X/lqJ3bMtdx
# 6nadBS63j/qSQ8Cl+YnUNxnXtqrwnIal2CWsDnkoOn7p0WfTxvspJ8fTeyOU5JEj
# lpB3gvmhhCNmElQzUHSxKCa7JGnCwlLyFGeKiUXULaGj6YgsIJWuHEqHCN8M9eJN
# YBi+qsSyrnAxZjNxPqxwoqvOf+l8y5Kh5TsxHM/q8grkV7tKtel05iv+bMt+dDk2
# DZDv5LVOpKnqagqrhPOsZ061xPeM0SAlI+sIZD5SlsHyDxL0xY4PwaLoLFH3c7y9
# hbFig3NBggfkOItqcyDQD2RzPJ6fpjOp/RnfJZPRAgMBAAGjggHNMIIByTASBgNV
# HRMBAf8ECDAGAQH/AgEAMA4GA1UdDwEB/wQEAwIBhjATBgNVHSUEDDAKBggrBgEF
# BQcDAzB5BggrBgEFBQcBAQRtMGswJAYIKwYBBQUHMAGGGGh0dHA6Ly9vY3NwLmRp
# Z2ljZXJ0LmNvbTBDBggrBgEFBQcwAoY3aHR0cDovL2NhY2VydHMuZGlnaWNlcnQu
# Y29tL0RpZ2lDZXJ0QXNzdXJlZElEUm9vdENBLmNydDCBgQYDVR0fBHoweDA6oDig
# NoY0aHR0cDovL2NybDQuZGlnaWNlcnQuY29tL0RpZ2lDZXJ0QXNzdXJlZElEUm9v
# dENBLmNybDA6oDigNoY0aHR0cDovL2NybDMuZGlnaWNlcnQuY29tL0RpZ2lDZXJ0
# QXNzdXJlZElEUm9vdENBLmNybDBPBgNVHSAESDBGMDgGCmCGSAGG/WwAAgQwKjAo
# BggrBgEFBQcCARYcaHR0cHM6Ly93d3cuZGlnaWNlcnQuY29tL0NQUzAKBghghkgB
# hv1sAzAdBgNVHQ4EFgQUWsS5eyoKo6XqcQPAYPkt9mV1DlgwHwYDVR0jBBgwFoAU
# Reuir/SSy4IxLVGLp6chnfNtyA8wDQYJKoZIhvcNAQELBQADggEBAD7sDVoks/Mi
# 0RXILHwlKXaoHV0cLToaxO8wYdd+C2D9wz0PxK+L/e8q3yBVN7Dh9tGSdQ9RtG6l
# jlriXiSBThCk7j9xjmMOE0ut119EefM2FAaK95xGTlz/kLEbBw6RFfu6r7VRwo0k
# riTGxycqoSkoGjpxKAI8LpGjwCUR4pwUR6F6aGivm6dcIFzZcbEMj7uo+MUSaJ/P
# QMtARKUT8OZkDCUIQjKyNookAv4vcn4c10lFluhZHen6dGRrsutmQ9qzsIzV6Q3d
# 9gEgzpkxYz0IGhizgZtPxpMQBvwHgfqL2vmCSfdibqFT+hKUGIUukpHqaGxEMrJm
# oecYpJpkUe8xggIoMIICJAIBATCBhjByMQswCQYDVQQGEwJVUzEVMBMGA1UEChMM
# RGlnaUNlcnQgSW5jMRkwFwYDVQQLExB3d3cuZGlnaWNlcnQuY29tMTEwLwYDVQQD
# EyhEaWdpQ2VydCBTSEEyIEFzc3VyZWQgSUQgQ29kZSBTaWduaW5nIENBAhACwXUo
# dNXChDGFKtigZGnKMAkGBSsOAwIaBQCgeDAYBgorBgEEAYI3AgEMMQowCKACgACh
# AoAAMBkGCSqGSIb3DQEJAzEMBgorBgEEAYI3AgEEMBwGCisGAQQBgjcCAQsxDjAM
# BgorBgEEAYI3AgEVMCMGCSqGSIb3DQEJBDEWBBRBq4Yyaxo2ZihLKGHzCApV8wzN
# YjANBgkqhkiG9w0BAQEFAASCAQBW1PSNLI/Kl12udbU1Q3TV2LOSD+EEC5wEWn9X
# Qx5l89esW1UwckAZO6SG6ItrqOATPXAU7W6EgP/QwH9BaigjdRAeI6Yeym4sDM8h
# 8jV8YPoMJXylEkgSsjZXeF66vu8Euk5WakE1tXfAsfYUumU/TRGo15yfNSqvkMdh
# U+M84XXiNVMHlu+0MPtvnxI9PoXUuB4lBsafjjmEkoqmVsl/Y2zn2zoZkDuly/DY
# vvau3Pl873m1ek2U7PtvBWuf2hTsWMIJWP2w9vlr5Kv1OSluBbsbSNdlDOlXYRSF
# 0WiID26B/9duWJDW0WOyp35+uztj0ZRghn4L6v7Xr+mmAx+y
# SIG # End signature block