We take pride in your success. We let our positivity drive us, day in and out. Talk to us at Mindfire to know us more.

Software Technology Tips

Home » Tips » Database » SQL Server: Restrict the duplicate insertion of records to the database using Store Procedure
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
(
      @Name varchar(50),
      @Qualification varchar(50)
) 
AS
DECLARE @Result int
BEGIN TRANSACTION 
IF EXISTS
(
      SELECT
            NULL
     FROM
            TestData WITH (UPDLOCK)
      WHERE
            [Name] = @Name AND
            Qualification = @Qualification
)
      BEGIN
            SELECT @Result = -1
      END
ELSE
      BEGIN
           INSERT INTO
               TestData
               (
                   [Name],
                   Qualification
               )
           VALUES
           (
                 @Name,
                 @Qualification
           )
           SELECT @Result = @@ERROR
     END
IF @Result <> 0
     BEGIN
            ROLLBACK
      END
ELSE
      BEGIN
            COMMIT
      END
RETURN @Result
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 :
EXEC usp_AddTestData_UsingExists 'Smith','Comp Sc'


Related Tags:

Database, SQL Server, Stored Procedures

Author: Rasmita Mohanty

top

Database

Let us Connect!

privacy

copyright (c) Mindfire Solutions 2007-2012. Login