CODEWITHSUNDEEP
c#sqlsql-serversql-server-2008
KX.T
KX.T

Reputation: 174

How can I prevent inserting duplicate data into a SQL Server table?

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?

Upvotes: 6

Views: 26236

Answers (4)

Rana Imran
Rana Imran

Reputation: 1

Try This Easy way

  { 
        DataSet ds = New DataSet();
SqlConnection myCnn = New SqlConnection(cnn);
myCnn.Open();
                    SqlCommand _Query = New SqlCommand("Select *FROM CamNo1 where platename='" + Console.ReadLine + "' ", myCnn);
                  SqlDataAdapter  sda = New SqlDataAdapter(_Query);
                    sda.Fill(ds);

                    Int i = ds.Tables[0].Rows.Count;
                    If (i > 0) Then
                                {
                        MessageBox.Show("platename" + Console.WriteLine + "Already Exists ");
                        ds.Clear();
                    }
                    Else
                    {
                        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", i`enter code here`mageFile`);
_Query.Parameters.AddWithValue("@filename", Name);

Try
{
   myCnn.Open();
   String checkname = (String)_Query.ExecuteScalar();
   myCnn.Close();

   getcheckname = checkname;
   Console.WriteLine("OK");
}
Catch (Exception)
{
}
                    }

  }

Upvotes: -1

Bert
Bert

Reputation: 82489

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

Upvotes: 6

Adam Rackis
Adam Rackis

Reputation: 83376

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)

Upvotes: 2

Adam Wenger
Adam Wenger

Reputation: 17570

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.

Upvotes: 6

Related Questions