sql – INSERT VALUES WHERE NOT EXISTS

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

所以在这个表中我有SoftwareName和SoftwareType.我试图得到它,如果条目页面发送插入查询与参数匹配表中的什么(所以相同的标题和类型),然后一个错误被抛出并且没有输入数据.

这样的事情

INSERT INTO tblSoftwareTitles( 
            SoftwareName,SoftwareSystemType) 
            VALUES(@SoftwareName,@SoftwareType) 
            WHERE NOT EXISTS (SELECT SoftwareName 
            FROM tblSoftwareTitles 
            WHERE Softwarename = @SoftwareName 
            AND SoftwareType = @Softwaretype)

所以这个语法非常适合将列从一个表中选择到另一个表中,而不会输入重复项,但似乎不希望使用参数化的插入查询.有人可以帮我吗？

干杯丹

编辑：

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

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语句：

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

您可以在不使用SELECT的情况下执行此操作

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

这两种方法都易于使用race condition,所以当我仍然使用上述方法之一插入时,但是您可以使用唯一的约束来保护重复的插入：

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

Example on SQL-Fiddle

附录

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

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

sql – INSERT VALUES WHERE NOT EXISTS

解决方法

猜你在找的MsSQL相关文章