How to insert only unique record in SQL Server using PowerShell-CodePudding

I'm inserting my CSV data into my sql server database using PowerShell but I'm just wondering how can I modify my sql query so that it will check if the GID and Source value is already matched in the Daily_Proc table and ONLY Insert if it's not existed.

Basically I don't want to insert the same record again and again in my database every time I run the script.

Function InsertError {
    $ErrorActionPreference = 'Stop'

    # check for file explicitly (in case a directory with that name exists)
    if (![System.IO.File]::Exists($Global:ErrorReport)) {
        # throwing an exception will abort the job
        write-log "Exiting script, no file found"
        throw (New-Object System.IO.FileNotFoundException("File not found: $Global:ErrorReport", $Global:ErrorReport))
    }
    else {
        $CSVImport = Import-CSV $Global:ErrorReport
        ForEach ($CSVLine1 in $CSVImport) {
            $CSVHold1 = $CSVLine1.Hold
            $CSVGID1 = $CSVLine1.GID
            $CSVSource1 = $CSVLine1.Source
            $CSVTYPE1 = $CSVLine1.TYPE
            $CSVMessage1 = $CSVLine1.Message
            $CSVCreatedDate1 = $CSVLine1.Time

            If ($CSVMessage1 -ne "The mailbox or SharePoint site may not exist.  If this is incorrect, please contact Microsoft support.  Otherwise, please remove it from this policy.") {
                $query = "USE $Global:Database
            INSERT INTO Daily_Proc (Hold, GID, Source, Type, CreatedDate, Status)
            VALUES('$CSVHold1', '$CSVGID1', '$($CSVSource1 -replace "'","''")','$CSVTYPE1', '$CSVCreatedDate1','Error-Retry');"

                Invoke-Sqlcmd -Query $query -ServerInstance $Global:Server -Database $Global:Database
            }
        }
    }
}

CodePudding user response：

So i believe you should be able to do it something like the below:

INSERT INTO Daily_Proc (Hold, GMID, Source, Type, CreatedDate, Status)
SELECT '$CSVHold1', '$CSVGMID1', '$($CSVSource1 -replace "'","''")','$CSVTYPE1', '$CSVCreatedDate1','Error-Retry'
WHERE NOT EXISTS (SELECT 1
                  FROM Daily_Proc
                  WHERE Hold = '$CSVHold1'
                  AND GMID = '$CSVGMID1'
                  AND Source = '$($CSVSource1 -replace "'","''")'
                  AND Type = '$CSVTYPE1'
                  AND CreatedDate = '$CSVCreatedDate1'
                  AND Status = 'Error-Retry')

The insert....select where not exists technique always serves me well for preventing dupes and pk violations.