INSERT VALUES WHERE NOT EXISTS

Question

OK so I'm trying to improve my asp data entry page to ensure that the entry going into my data table is unique. So in this table I have SoftwareName and SoftwareType. I'm trying to get it so if the entry page sends an insert query with parameters that match whats in the table (so same title and type) then an error is thrown up and the Data isn't entered. Something like this: ``` INSERT INTO tblSoftwareTitles( SoftwareName, SoftwareSystemType) VALUES(@SoftwareName,@SoftwareType) WHERE NOT EXISTS (SELECT SoftwareName FROM tblSoftwareTitles WHERE Softwarename = @SoftwareName AND SoftwareType = @Softwaretype) ``` So this syntax works great for selecting columns from one table into another without duplicates being entered but doesn't seem to want to work with a parametrized insert query. Can anyone help me out with this? Edit: Here's the code I'm using in my ASP insert method ``` private void ExecuteInsert(string name, string type) { //Creates a new connection using the HWM string using (S…

Accepted Answer

You could do this using an `IF` statement:

```
IF NOT EXISTS 
    (   SELECT  1
        FROM    tblSoftwareTitles 
        WHERE   Softwarename = @SoftwareName 
        AND     SoftwareSystemType = @Softwaretype
    )
    BEGIN
        INSERT tblSoftwareTitles (SoftwareName, SoftwareSystemType) 
        VALUES (@SoftwareName, @SoftwareType) 
    END;
```

You could do it without `IF` using `SELECT`

```
INSERT  tblSoftwareTitles (SoftwareName, SoftwareSystemType) 
SELECT  @SoftwareName,@SoftwareType
WHERE   NOT EXISTS 
        (   SELECT  1
            FROM    tblSoftwareTitles 
            WHERE   Softwarename = @SoftwareName 
            AND     SoftwareSystemType = @Softwaretype
        );
```

Both methods are susceptible to a race condition, so while I would still use one of the above to insert, but you can safeguard duplicate inserts with a unique constraint:

```
CREATE UNIQUE NONCLUSTERED INDEX UQ_tblSoftwareTitles_Softwarename_SoftwareSystemType
    ON tblSoftwareTitles (SoftwareName, SoftwareSystemType);
```

Example on SQL-Fiddle

ADDENDUM

In SQL Server 2008 or later you can use `MERGE` with `HOLDLOCK` to remove the chance of a race condition (which is still not a substitute for a unique constraint).

```
MERGE tblSoftwareTitles WITH (HOLDLOCK) AS t
USING (VALUES (@SoftwareName, @SoftwareType)) AS s (SoftwareName, SoftwareSystemType) 
    ON s.Softwarename = t.SoftwareName 
    AND s.SoftwareSystemType = t.SoftwareSystemType
WHEN NOT MATCHED BY TARGET THEN 
    INSERT (SoftwareName, SoftwareSystemType) 
    VALUES (s.SoftwareName, s.SoftwareSystemType);
```

Example of Merge on SQL Fiddle

INSERT VALUES WHERE NOT EXISTS

Solution

Problem