在不存在的地方插入值

本文介绍了在不存在的地方插入值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

好的，所以我正在尝试改进我的 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 上的合并示例