IF SQL @ID EXISTS, DO NOT INPUT?

Question

I made a Powershell script that inputs data from a Powershell O365 Health Module into a SQL Database by using a Foreach loop. The issue I am having now is that each time I run the script (every 30 minutes or something), it creates new lines into my database, and most of the time it's duplicates.

One of the values (@ID) is unique, so what I want to do is: If @ID FROM table exist, DO Nothing. So it should then only skip that line in the Foreach statement and continue into the next one and continue writing the others into the table.

I am kinda new to SQL so I am a bit unsure how to do this.

Here's my script:

$o365user = 'user@company.com'
Import-Module O365ServiceCommunications
$Credential = Get-Credential $o365user
$Session = New-SCSession -Credential $Credential -locale en-US

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection;
$SqlConnection.ConnectionString = 'Server=testcoresight;Database=O365Health;Integrated Security=True'
$SqlConnection.Open();

$sql = @'
            INSERT INTO O365Events( 
                StartTime, 
                EndTime, 
                ID, 
                EventType, 
                Service, 
                Status
            )
            VALUES (
                @StartTime, 
                @EndTime, 
                @ID, 
                @EventType, 
                @Service, 
                @Status
            )
'@
$cmd = $SqlConnection.CreateCommand()
$cmd.CommandTimeout = 15
$cmd.CommandText = $sql

[void]$cmd.Parameters.Add('@StartTime',[string])
[void]$cmd.Parameters.Add('@EndTime',[string])
[void]$cmd.Parameters.Add('@ID',[string])
[void]$cmd.Parameters.Add('@EventType',[string])
[void]$cmd.Parameters.Add('@Service',[string])
[void]$cmd.Parameters.Add('@Status',[string])

#Gets O365 Health Status. Shows Incident types for the past 7 days and sends them to the SQL DB.
Get-SCEvent -SCSession $Session -EventTypes Incident -pastdays 7 | 
    ForEach-Object {
        Try{
            $cmd.Parameters['@StartTime'].Value = if($_.StartTime){$_.StartTime}else{''}
            $cmd.Parameters['@EndTime'].Value = if ($_.EndTime) { $_.EndTime } else { '' }
            $cmd.Parameters['@ID'].Value = if ($_.ID) { $_.ID } else { '' }
            $cmd.Parameters['@EventType'].Value = if ($_.EventType) { $_.EventType } else { '' }
            $cmd.Parameters['@Service'].Value = if ($_.AffectedServiceHealthStatus.ServiceName) { $_.AffectedServiceHealthStatus.ServiceName } else { '' }
            $cmd.Parameters['@Status'].Value = if ($_.Status) { $_.Status } else { '' }
            Write-Host ID Loaded: $_.ID -ForegroundColor green

            if ($cmd.ExecuteNonQuery() -ne 1) {
                    Write-Host Insert failed: $_.ID
            }
        }
        Catch{ Write-Host $_ }
    }
$SqlConnection.Close()

What I believe I have to do is add something like:

IF @ID EXISTS FROM O365Events skip

Starting with SQL Server 2008 you should use [MERGE](https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql) for this... This allows you to insert / update and/or delete in one single go. It is used to synchronize tables. — Shnugo, Jun 14 '17 at 08:23
Thanks Scnugo. Do you mean I could just do this?: MERGE INTO O365Events( StartTime, EndTime, ID, EventType, Service, Status — MPC, Jun 14 '17 at 08:25
Well, click the link to read the details... `MERGE` as a *source* and a *target* result set. Then you define a condition to check the existance of rows. Three sections can be defined: not existing in target (usually insert as new row), existing (usually update existing values with new values) and not existing in source (usually delete in source or insert to target). This can be solved in a procedural way with `IF` too, but in one single go it is much better to optimize... — Shnugo, Jun 14 '17 at 08:29
I think merge is an overkill for insert only. Please read [this SO post](https://stackoverflow.com/questions/38497259/what-is-the-best-practice-for-inserting-a-record-if-it-doesnt-already-exist), and it's comments (especially the one with the links to Dan Guzman's blog). — Zohar Peled, Jun 14 '17 at 09:23
Actually, the linked MSDN doc on MERGE says it's overkill for insert only.. — Caius Jard, Jun 14 '17 at 09:34

score 1 · Accepted Answer · answered Jun 14 '17 at 09:33

        INSERT INTO O365Events( 
            StartTime, 
            EndTime, 
            ID, 
            EventType, 
            Service, 
            Status
        )
        SELECT
            @StartTime, 
            @EndTime, 
            @ID, 
            @EventType, 
            @Service, 
            @Status
        WHERE NOT EXISTS (SELECT null FROM O365Events WHERE ID = @ID)

Or, really, you could just make the ID the promary key and handle the error that occurs when you try to insert a duplicate

I was eating lunch and reading the documentation for merging, I get back, see this, try it, and works :) Thanks! — MPC, Jun 14 '17 at 11:17

IF SQL @ID EXISTS, DO NOT INPUT?

1 Answers1