I'm inserting into a SQL database from multiple processes. It's likely that the processes will sometimes try to insert duplicate data into the table. I've tried to write the query in a way that will handle the duplicates but I still get:

```
System.Data.SqlClient.SqlException: Violation of UNIQUE KEY constraint 'UK1_MyTable'. Cannot insert duplicate key in object 'dbo.MyTable'.
The statement has been terminated.

```

My query looks something like:

```
INSERT INTO MyTable (FieldA, FieldB, FieldC)
SELECT FieldA='AValue', FieldB='BValue', FieldC='CValue'
WHERE (SELECT COUNT(*) FROM MyTable WHERE FieldA='AValue' AND FieldB='BValue' AND FieldC='CValue' ) = 0

```

The constraint 'UK1\_MyConstraint' says that in MyTable, the combination of the 3 fields should be unique.

My questions:

1. Why doesn't this work?
2. What modification do I need to make so there is no chance of an exception due to the constraint violation?

Note that I'm aware that there are other approaches to solving the original problem of "INSERT if not exists" such as (in summary):

* Using TRY CATCH
* IF NOT EXIST INSERT (inside a transaction with serializable isolation)

Should I be using one of the approaches?

**Edit 1** SQL for Creating Table:

```
CREATE TABLE [dbo].[MyTable](
  [Id] [bigint] IDENTITY(1,1) NOT NULL,
  [FieldA] [bigint] NOT NULL,
  [FieldB] [int] NOT NULL,
  [FieldC] [char](3) NULL,
  [FieldD] [float] NULL,
  CONSTRAINT [PK_MyTable] PRIMARY KEY NONCLUSTERED 
  (
    [Id] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON),
  CONSTRAINT [UK1_MyTable] UNIQUE NONCLUSTERED 
  (
    [FieldA] ASC,
    [FieldB] ASC,
    [FieldC] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)

```

**Edit 2** Decision:

Just to update this - I've decided to use the "JFDI" implementation suggested in the linked question ([link](https://stackoverflow.com/questions/3407857/only-inserting-a-row-if-its-not-already-there/3408196#3408196)). Although I'm still curious as to why the original implementation doesn't work.