There are several ways we can avoid duplicate record insertion in the database using various programming languages. But, it doesnt have to be from programs always we can avoid duplicate insertion of records from the database too.
Here I am going to explain one of the methods by which we can avoid duplicate record insertion in the SQL Server database using store procedure, incase the database does not contains any unique field.
In SQL Server Database we can check the record before insert to the database and insert the record to the database by creating a store procedure as follows:
CREATE PROCEDURE usp_AddTestData_UsingExists
DECLARE @Result int
TestData WITH (UPDLOCK)
[Name] = @Name AND
Qualification = @Qualification
SELECT @Result = -1
SELECT @Result = @@ERROR
IF @Result <> 0
Here I am using the [TestData] as Table name. [Name] and [Qualification] are the two columns in the table and through this store procedure we can insert data to the table after checking if the record exists or not in the TestData table.
If the record is already exists then it ignores the insert operation for that particular record and if the record does not exist then it inserts the record in the database and commits the process.
To InsertData we need two parameters for Name and Qualification column in TestData table such as :