locked
Check If Data Already Exists Before Insert RRS feed

  • Question

  • What would be the best way to check if the data exists before insert, so duplicates are not created?  Let's say that I have a tbl_CustomerInfo and I have a userID of 123456 in the table, if someone tries to input 12345 again, I do not want to permit it.  This is the Insert statement that I have in play.  The thought that I have to check is to 1st run a select statement against the database and if the userID is found don't insert, if it is not found then run the insert.  Would that be my best "plan of attack" for such?

    string cmdPgm = "INSERT INTO tbl_CustomerInfo (ABC,UserID) VALUES (@ABC, @UserID)";
    
    using (SqlConnection conn = new SqlConnection(connString.SqlServerConnString))
    {
    	using (SqlCommand comm = new SqlCommand())
    	{
    		comm.Connection = conn;
    		comm.CommandText = cmdPgm;
    		comm.Parameters.AddWithValue("@ABC", txt_ABC.Text);
    		comm.Parameters.AddWithValue("@UserID", txtUserID.Text);
    		try 
    		{ 
    			conn.Open(); 
    			comm.ExecuteNonQuery();
    			conn.Close();
    		}
    		catch (SqlException e) { MessageBox.Show(e.ToString()); }
    	}
    }

    Thursday, March 19, 2015 6:27 PM

Answers

  • DECLARE @count INT
    SELECT @count = COUNT(*) FROM tablename WHERE useid= @userid
    IF @count <= 0 BEGIN
     // INSERT HERE
    END



    PS.Shakeer Hussain

    • Marked as answer by IndigoMontoya Sunday, March 22, 2015 9:09 PM
    Thursday, March 19, 2015 7:34 PM

All replies

  • I think that one of the reliable solutions is to make UserID Primary Key. If the table already has such a key, then define a special index for this column: a Unique Key. You will receive an exception. Some of the members will allow you to distinguish it.

    • Edited by Viorel_MVP Thursday, March 19, 2015 6:42 PM
    • Proposed as answer by pvdg42 Thursday, March 19, 2015 6:56 PM
    Thursday, March 19, 2015 6:41 PM
  • First thing you should do is constrain your schema so duplicates are not allowed where you do not want them. Then there is no way they could make it into your system even if you (or someone) has a defect in the code that could potentially create one. In the example above make userid a clustered primary key. If you do not want a duplicate user name then create a unique index constraint on username. This ensures your data can never be invalid/corrupted.

    How to check before creating a new record. You can handle this in a few different ways, here are two types that I see most frequently.

    1. Insert and then handle the error in a catch. Because the data model / schema will not allow a duplicate it will throw an exception. You can then return an error or execute an update in your catch block. The upside is its not a lot of code. Downside is you are relying on exceptions and its not very clean and can be slow if you are doing many records in one go. This approach is great to use if the chance of duplicates is low to begin with, why write extra code for something that will almost never occur in normal operations.
    2. Start a transaction, execute a serialized read on the table to check if there is an existing value, and then do the insert/update accordingly, and commit the transaction. Upside is this is clean, downside is that you lock the table temporarily. This can be coded in C# OR you could create a stored procedure and execute the same logic in sql directly. This is a good approach if duplicates commonly occur or if you are doing batch inserts/updates.


    -Igor

    Thursday, March 19, 2015 6:57 PM
  • That's the easiest solution, but unfortunately I am unable to alter the table :(  

    As much as I hate to do this, the option I am trying to create is to band-aid a broken process as I can't take it back to ground zero and build it properly from the start.

    Thursday, March 19, 2015 6:57 PM
  • In that case I would suggest you to create a stored procedure which checks if the data exists or not and then performs the insertion.

    Other option will be modify you query to include the logic for checking the duplicate.


    Thanks,
    Prashant
    ----------------------------------------
    Please mark this post accordingly if it answers your query or is helpful.

    Thursday, March 19, 2015 7:04 PM
  • If you cannot affect the table, then consider the MERGE statement too (if available in your environment):

    MERGE INTO tbl_CustomerInfo AS target

    USING (SELECT @ABC, @UserID) AS source (ABC, UserID)

    ON target.UserID = source.UserID

    WHEN NOT MATCHED THEN

           INSERT (ABC, UserID)

           VALUES (source.ABC, source.UserID)

    ;


    The integer number returned by ExecuteNonQuery will reflect the result.


    • Edited by Viorel_MVP Thursday, March 19, 2015 7:11 PM
    Thursday, March 19, 2015 7:08 PM
  • Here is a code sample to illustrate what I mentioned previously, it uses a table hint and transaction to ensure that no race conditions could still create a duplicate value. You could write this in C# or you could write a stored procedure and call it instead. Or you can use the Merge statement that Viorel_ proposed which is a bit cleaner looking in my opinion.

    create table #MyUsers(
    	UserId int not null,
    	UserName varchar(50) not null
    )
    go
    
    insert into #MyUsers(UserId, UserName) values(1,'test')
    GO
    
    DECLARE @userId int = 1
    declare @userName varchar(50) = 'newname'
    
    begin transaction myinsert
    	DECLARE @userFound int
    	if(exists(SELECT UserId FROM #MyUsers with (SERIALIZABLE) WHERE UserId = @userId))
    	begin
    		update #MyUsers set UserName = @userName where UserId = @userId
    	end else
    	begin
    		insert into #MyUsers(UserId, UserName) values(@userId,@userName)
    	end
    commit transaction myinsert


    -Igor

    Thursday, March 19, 2015 7:15 PM
  • DECLARE @count INT
    SELECT @count = COUNT(*) FROM tablename WHERE useid= @userid
    IF @count <= 0 BEGIN
     // INSERT HERE
    END



    PS.Shakeer Hussain

    • Marked as answer by IndigoMontoya Sunday, March 22, 2015 9:09 PM
    Thursday, March 19, 2015 7:34 PM