在不存在的地方插入值 [英] INSERT VALUES WHERE NOT EXISTS

问题描述

好的，所以我正在尝试改进我的 asp 数据输入页面，以确保进入我的数据表的条目是唯一的.

OK so I'm trying to improve my asp data entry page to ensure that the entry going into my data table is unique.

所以在这个表中我有 SoftwareName 和 SoftwareType.我正在尝试获取它，因此如果条目页面发送的插入查询的参数与表中的内容匹配(因此标题和类型相同)，则会引发错误并且未输入数据.

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.

像这样:

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?

这是我在 ASP 插入方法中使用的代码

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);
        }

    }
}

推荐答案

您可以使用 IF 语句执行此操作:

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;

你可以不用 IF 使用 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);

SQL-Fiddle 示例

附录

在 SQL Server 2008 或更高版本中，您可以使用 MERGE 和 HOLDLOCK 来消除竞争条件的可能性(这仍然不能替代唯一约束).

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);

SQL Fiddle 上的合并示例

这篇关于在不存在的地方插入值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！