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 (SqlConnection HWM = new SqlConnection(GetConnectionStringHWM()))     {         //Creates a sql string with parameters         string sql = " INSERT INTO tblSoftwareTitles( "                    + " SoftwareName, "                     + " SoftwareSystemType) "                    + " SELECT "                    + " @SoftwareName, "                    + " @SoftwareType "                    + " WHERE   NOT EXISTS  "                    + " ( SELECT  1 "                    + " FROM tblSoftwareTitles "                    + " WHERE Softwarename = @SoftwareName "                    + " AND SoftwareSystemType = @Softwaretype); ";                   //Opens the connection         HWM.Open();         try         {             //Creates a Sql command             using (SqlCommand addSoftware = new SqlCommand{                 CommandType = CommandType.Text,                 Connection = HWM,                 CommandTimeout = 300,                 CommandText = sql})             {                 //adds parameters to the Sql command                 addSoftware.Parameters.Add("@SoftwareName", SqlDbType.NVarChar, 200).Value = name;                 addSoftware.Parameters.Add("@SoftwareType", SqlDbType.Int).Value = type;                 //Executes the Sql                 addSoftware.ExecuteNonQuery();             }             Alert.Show("Software title saved!");         }         catch (System.Data.SqlClient.SqlException ex)         {             string msg = "Insert Error:";             msg += ex.Message;             throw new Exception(msg);         }      } } 

Answer 1

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