How to prevent insert duplicate in SQL Server

I have a stored procedure to insert data into 2 tables. I uses begin and commit transaction. But my if statement before the 2nd insert is not working. Now I have a duplicate entry not only on the first table but as well as on the 2nd table. Below is my stored procedure and hopefully someone can assist me.

USE [DhipayaHQDB_Test]
GO
/****** Object: StoredProcedure [dbo].[spInsertMotorInsuranceShortTerm_transaction] Script Date: 8/9/2017 4:19:46 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[spInsertMotorInsuranceShortTerm_transaction]
-- Add the parameters for the stored procedure here
(
@MotorInsuranceID int,
@RefNumber nvarchar(15),
@IssuanceType nvarchar(50),
@CustomerNo nvarchar(50),
@VehicleType nvarchar(50),
@VehicleBrand nvarchar(50),
@VehiclePlateNo nvarchar(50),
@VehicleEngineNo nvarchar(50),
@VehicleChasisNo nvarchar(50),
@VehiclePower nvarchar(50),
@InsuranceType nvarchar(50),
@InsuranceOption nvarchar(50),
@PeriodFrom date,
@PeriodTo date,
@ApplicationTime nvarchar(50),
@DateIssued date,
@NetPremium decimal(18,2),
@RegistrationFee decimal(18,2),
@VAT decimal(18,2),
@TotalPremium decimal(18,2),
@BranchCode varchar(50),
@AgentID nvarchar(50),
@Username varchar(50),
--@TransactionNumber nvarchar(50),
@AgentGrossCommission decimal(18,2),
@AgentTax decimal(18,2),
@AgentNetCommission decimal(18,2),
@ProductType varchar(50),
@Seller nvarchar(50),
@Remarks nvarchar(50),
--customer area
@CustomerID int,
@ReferenceNo nvarchar(50),
@CustomerName nvarchar(255),
@TaxPayersID nvarchar(50),
@Village nvarchar(50),
@District nvarchar(50),
@Province nvarchar(50),
@MobilePhone nvarchar(50),
@CustTelephone nvarchar(50),
@CustEmail nvarchar(50),
@Agent nvarchar(50),
@Status nvarchar(50),
@TrailDate nvarchar(50),
@TrailTime nvarchar(50))
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
-- insert statement for tblMotorInsurance_eCI
INSERT INTO [dbo].[tblMotorInsurance_eCI]
(
[RefNumber]
,[IssuanceType]
,[CustomerNo]
,[VehicleType]
,[VehicleBrand]
,[VehiclePlateNo]
,[VehicleEngineNo]
,[VehicleChasisNo]
,[VehiclePower]
,[InsuranceType]
,[InsuranceOption]
,[PeriodFrom]
,[PeriodTo]
,[ApplicationTime]
,[DateIssued]
,[NetPremium]
,[RegistrationFee]
,[VAT]
,[TotalPremium]
,[BranchCode]
,[AgentID]
,[Username]
--,[TransactionNumber]
,[AgentGrossCommission]
,[AgentTax]
,[AgentNetCommission]
,[ProductType]
,[Seller]
,[Remarks]
)
VALUES
(
@RefNumber,
@IssuanceType,
@CustomerNo,
@VehicleType,
@VehicleBrand,
@VehiclePlateNo,
@VehicleEngineNo,
@VehicleChasisNo,
@VehiclePower,
@InsuranceType,
@InsuranceOption,
@PeriodFrom,
@PeriodTo,
@ApplicationTime,
@DateIssued,
@NetPremium,
@RegistrationFee,
@VAT,
@TotalPremium,
@BranchCode,
@AgentID,
@Username,
--@TransactionNumber,
@AgentGrossCommission,
@AgentTax,
@AgentNetCommission,
@ProductType,
@Seller,
@Remarks
)
-- insert statement for tblcustomers_shortterm
if exists (select * from tblCustomers_ShortTerm where CustomerNo=@CustomerNo)
begin
INSERT INTO [dbo].[tblCustomers_ShortTerm]
(
[ReferenceNo]
,[CustomerNo]
,[CustomerName]
,[TaxPayersID]
,[Village]
,[District]
,[Province]
,[CustTelephone]
,[MobilePhone]
,[CustEmail]
,[AgentID]
,[Status]
,[Username]
,[TrailDate]
,[TrailTime])
VALUES
(
@ReferenceNo,
@CustomerNo,
@CustomerName,
@TaxPayersID,
@Village,
@District,
@Province,
@MobilePhone,
@CustTelephone,
@CustEmail,
@Agent,
@Status,
@Username,
@TrailDate,
@TrailTime)
end
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
RAISERROR('An error occured. All entries are reverted to it original state.',16,1)
END CATCH
END

Thanks in advance.

One query:

Now I have a duplicate entry not only on the first table but as well as on the 2nd table: As per your condition it will insert records, how can you say duplicate records. You are using if-exits keywords that will help to avoid duplicate records(you should use that properly).

Let's say, it inserts 1 record in 1st table but second condition fail. So you don't need 1st inserted records, correct? If so: you can use execute 1st insert statement inside if-exists condition or if condition fails then you can roll back the transaction like below:

IF EXISTS (select * from tblCustomers_ShortTerm where CustomerNo=@CustomerNo)
BEGIN
-- Your insert statement
END
ELSE
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Dulicate record found. All entries are reverted to it original state.',16,1)
END

9 Replies

Know the answer? Post it — somebody with the same question will find it here.

Manas Mohapatra9y ago
Accepted answer
One query:

Now I have a duplicate entry not only on the first table but as well as on the 2nd table: As per your condition it will insert records, how can you say duplicate records. You are using if-exits keywords that will help to avoid duplicate records(you should use that properly).

Let's say, it inserts 1 record in 1st table but second condition fail. So you don't need 1st inserted records, correct? If so: you can use execute 1st insert statement inside if-exists condition or if condition fails then you can roll back the transaction like below:
IF EXISTS (select * from tblCustomers_ShortTerm where CustomerNo=@CustomerNo)
BEGIN
    -- Your insert statement
END
ELSE
BEGIN
    ROLLBACK TRANSACTION
    RAISERROR('Dulicate record found. All entries are reverted to it original state.',16,1)
END
Nilesh Shah9y ago
just move the begin transaction statement above the if statement
Manas Mohapatra9y ago
Try like below:
IF EXISTS (select * from tblCustomers_ShortTerm where CustomerNo=@CustomerNo)
    BEGIN TRY

        BEGIN TRANSACTION
             -- Multiple Inserts
            INSERT INTO....
            INSERT INTO....
            INSERT INTO....
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
     IF (@@TRANCOUNT > 0)
     BEGIN
        ROLLBACK TRANSACTION
        RAISERROR('Exception occurred. All entries are reverted to it original state.',16,1)
     END
    END CATCH
END
ELSE
BEGIN
    RAISERROR('Dulicate record found. All entries are reverted to it original state.',16,1)
END
Jes Sie9y ago
Hello again everyone, I was hoping somebody can help me with this error:

Below is my stored procedure:

Thanks in advance
Jes Sie9y ago
Thanks for all your help. All of your suggestions really helped. Mostly to Manas who pointed out how to structure by query. Here's my final structure.
Jes Sie9y ago
Thanks for all your suggestions and recommendations. I will try all you recommendations and will revert for any results.
Tapan Patel9y ago
"If not exist " is one of the choices. Or you can just create a unique index (in this case, it will prevent extra check of "if not exist", thus making SP little bit faster) across the columns (for which you don't want redundancy) and that way, you will never have a duplication, if it tries to insert, it will fail and raise an error (as per your code).
Nilesh Shah9y ago
first you need to identify the unique columns on which you will decide if it is a duplicate record or not,
then just before the insert statement, write a select query on those columns in where condition with "if not exist", so that insert will only happen if no record is found.
I see you have done something similar before the second insert statement, but instead of "if not exist" you have written "if exist"
Gokhul Varman9y ago
Please refer the link for your solution about SQL:https://stackoverflow.com/questions/2513174/how-to-avoid-duplicates-in-insert-into-select-query-in-sql-server