How can I prevent inserting duplicate data into a SQL Server table?
Solution 1
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.
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
Solution 2
If you do not want duplicate data, you should consider enforcing that at the DB level with a UNIQUE CONSTRAINT or a UNIQUE INDEX
SQL Server 2008 also has a MERGE statement you could use to check for matched records. This could be helpful if you want to update an existing record.
Solution 3
If you want to prevent duplicate data from being inserted, you could use a unique index or unique constraint on those fields.
If you want to just run a hard insert statement, but have it do nothing if a value exists, something like this should work. I tested this on a local database I have:
declare @subject as varchar(100);
set @subject = 'hello'
insert into Subjects ([name])
select @subject
where not exists (select 1 from Subjects where [name] = @Subject)
kxtan
Updated on April 03, 2020Comments
-
kxtan about 4 years
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?
Example data to be inserted:
David James John
If the 4th data is
John
again, I want the system to skip the duplicate record (John).So far I have:
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) { }
i got the string value checkname that is last inserted, what should i do check the data?