如何防止将重复数据插入SQL Server表中? [英] How can I prevent inserting duplicate data into a SQL Server table?
问题描述
我有一系列需要写入SQL的数据,我应该怎么做才能检查SQL中的数据以防止将相同的数据插入表中?
I have a series of data that need to be written into SQL, what should I do to check the data in SQL to prevent same data inserted into table?
要插入的示例数据:
David
James
John
如果第四个数据再次是 John
,我希望系统跳过重复的记录(约翰)。
If the 4th data is John
again, I want the system to skip the duplicate record (John).
到目前为止,我有:
SqlConnection myCnn = new SqlConnection(cnn);
String _state = "Insert into CamNo1(platename, date, camID, path, filename) OUTPUT INSERTED.platename values(@msg, getdate(), @camID, @path, @filename)";
SqlCommand _Query = new SqlCommand(_state, myCnn);
_Query.Parameters.AddWithValue("@msg", msg);
_Query.Parameters.AddWithValue("@camID", camID);
_Query.Parameters.AddWithValue("@path", imageFile);
_Query.Parameters.AddWithValue("@filename", name);
try
{
myCnn.Open();
string checkname = (string)_Query.ExecuteScalar();
myCnn.Close();
getcheckname = checkname;
Console.WriteLine("OK");
}
catch (Exception)
{
}
我得到了最后插入的字符串值checkname,我应该怎么检查数据?
i got the string value checkname that is last inserted, what should i do check the data?
推荐答案
首先,您可以通过使用唯一索引或约束来防止表中出现重复项。索引/约束可以按照以下建议协同工作。如果仅使用唯一索引而不是以下解决方案之一,则插入重复记录将引发错误,并且您将需要在另一端进行处理。
First, you can prevent a duplicate from ever occurring in the table by using a unique index or constraint. An index/constraint can work in concert with the suggestions below. If you only use a unique index and not one of the below solutions, inserting a duplicate record will throw an error and you will need to handle that on the other end.
另外,我可能会通过存储过程插入数据,以检查该行是否已存在。为此,您可以使用 MERGE 语句,如伪代码所示:
Additionally, I would probably insert the data via a stored procedure that checks to see if the row already exists. To do that, you can use either a MERGE statement, as shown in this pseudo code:
create procedure MyProcedure
(
@Name nvarchar(100),
...
)
as
merge MyTable
using
(
select @Name,...
) as source (Name, ...)
on MyTable.Name = source.Name
when not matched then
insert (Name,...) values (source.Name,...)
when matched then
update set Name = @Name,...
或者,您可以检查记录是否存在并手动插入或更新:
or, you could check for the records existence and insert or update manually:
create procedure MyProcedure
(
@Name nvarchar(100),
...
)
as
if not exists (select * from MyTable where Name = @Name)
begin
insert into MyTable (Name,...) values (@Name,...)
end
else
begin
update MyTable
set ...
where Name = @Name
end
这篇关于如何防止将重复数据插入SQL Server表中?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!