How to avoid duplicate records using stored procedure

Question

I want to write a stored procedure to insert data into a table and also to check whether same data already exist?

If yes, then exception throw as already exist. But I don't know where should I add an exception. Please help.

ALTER PROCEDURE [dbo].[datakutipantest1]
AS
BEGIN
    DECLARE @ModifiedDate datetime = GETDATE()

    INSERT INTO spk_DataKutipan ([NO_BIL], [NO_AKAUN], [TKH_BAYAR],
  [STESYEN], [AMAUN_BAYAR], [JENIS_BAYAR], [NO_RESIT], [STATUS], [NO_VOT], [TKH_MODIFIKASI])
        SELECT
            D.BillNo,
            D.AccountNo,
            D.TxDate,
            D.ReferenceCode,
            D.Amount,
            PaymentTypeId,
            D.ReferenceNo,
            D.Status,
            D.RevenueCode,
            @ModifiedDate
        FROM 
            (SELECT
                 B.ComponentId,
                 B.AccountNo,
                 B.BillNo,
                 B.RevenueCode,
                 B.Amount,
                 B.TxId,
                 ReferenceNo,
                 B.ReferenceCode,
                 status,
                 TxDate
             FROM 
                 (SELECT
                      A.ComponentId,
                      A.TxId,
                      AccountNo,
                      BillNo,
                      RevenueCode,
                      Amount,
                      C.ReferenceCode
                  FROM 
                      rcs_TxBillItem A
                  INNER JOIN 
                      (SELECT
                           ComponentId,
                           ComponentName,
                           ReferenceCode
                       FROM 
                           rcs_Component
                       WHERE  
                           IsDeleted = 0) C ON C.ComponentId = A.ComponentId) B
                  INNER JOIN  
                      rcs_TxBill P ON P.TxId = B.TxId) D

             INNER JOIN 
                 rcs_TxBillPayment E ON E.TxId = D.TxId
END
GO

Do you have any unique key in the spk_DataKutipan table? If you have unique key, SQL will automatically throw error when you try to insert duplicate — PSK, May 03 '18 at 04:17

score 2 · Accepted Answer · answered May 03 '18 at 04:25

CREATE PROCEDURE [dbo].[Useradd]
      @username varchar(20),
      @pword nvarchar(20),
      @empname varchar(20),
      @email nvarchar(50),
      @designation varchar(20),
      @reportto varchar(20) AS
  IF (SELECT COUNT(*) FROM Users WHERE username= @username) < 1
    BEGIN   
      INSERT INTO Users(username,pword,empname,email,designation,reportto)    
      VALUES(@username,@pword,@empname,@email,@designation,@reportto)
    END

score 0 · Answer 2 · answered May 03 '18 at 07:04

Consider to use MERGE

MERGE <target_table> [AS TARGET]
USING <table_source> [AS SOURCE]
ON <search_condition> --For example ColumnSource = ColumnTable
WHEN MATCHED --Rows already exist
   THEN UPDATE target_table SET ... --Do Update for example
WHEN NOT MATCHED BY TARGET --Rows not exist on Target Table
   THEN INSERT(...) VALUES(...) --Do Insert for example
WHEN NOT MATCHED BY SOURCE --Rows not exist on Source table but exists on Target Table
   THEN DELETE ... --Do Delete for example

The MERGE statement basically merges data from a source result set to a target table based on a condition that you specify and if the data from the source already exists in the target or not. The new SQL command combines the sequence of conditional INSERT, UPDATE and DELETE commands in a single atomic statement, depending on the existence of a record.

How to avoid duplicate records using stored procedure

2 Answers2