how to check if the record exists before insert to prevent duplicates?

Question

I want to select CustomerID, OrderTypeID, and LoanNumber from tblOrder and insert it with a new OrderTypeID but same CustomerID and LoanNumber. My query does that but it duplicates the values, every time the query is executed it duplicates the rows.

insert into tblOrder (CustomerID, OrderTypeID, LoanNumber)
Select o.CustomerID,3, o.LoanNumber
from tblOrder as o
where o.OrderTypeID = 1;

this is what the query does:

OrderID 9 - 12 are duplicates or OrderID 5 - 8. The query was executed 2 times hence the records got duplicated.

Answer 1

The first, and most important, issue here is not that your query inserts duplicates; it is that your table allows it to happen in the first place. Hence, you first need to create a UNIQUE INDEX on those 3 fields to disallow duplicates.

The second issue is how to handle the situation of when an operation attempts to insert a duplicate. You have two main choices:

1. You can check for the record's existence first and skip the INSERT if it is found, or

2. You can set the UNIQUE INDEX to "ignore" duplicates in which case you don't need to check first as the operation will silently fail, with just a warning that the duplicate was not inserted.

---

If you pick Option #1 (check first), then:

CREATE UNIQUE NONCLUSTERED INDEX [UIX_tblOrder_CustomerID_OrderTypeID_LoanNumber]
    ON [tblOrder]
    ( [CustomerID] ASC, [OrderTypeID] ASC, [LoanNumber] ASC );

And then:

INSERT INTO tblOrder (CustomerID, OrderTypeID, LoanNumber)
  SELECT o.CustomerID, 3, o.LoanNumber
  FROM   tblOrder as o
  WHERE  o.OrderTypeID = 1
  AND    NOT EXISTS (SELECT *
                     FROM tblOrder tmp
                     WHERE tmp.CustomerID = o.CustomerID
                     AND   tmp.OrderTypeID = 3
                     AND   tmp.LoanNumber = o.LoanNumber);

If you pick Option #2 (don't check), then:

CREATE UNIQUE NONCLUSTERED INDEX [UIX_tblOrder_CustomerID_OrderTypeID_LoanNumber]
    ON [tblOrder]
    ( [CustomerID] ASC, [OrderTypeID] ASC, [LoanNumber] ASC )
    WITH (IGNORE_DUP_KEY = ON);

And then:

INSERT INTO tblOrder (CustomerID, OrderTypeID, LoanNumber)
  SELECT o.CustomerID, 3, o.LoanNumber
  FROM   tblOrder as o
  WHERE  o.OrderTypeID = 1;

---

Example of how IGNORE_DUP_KEY behaves:

CREATE TABLE #IgnoreDuplicateTest (Col1 INT);
CREATE UNIQUE NONCLUSTERED INDEX [UIX_#IgnoreDuplicateTest_Col1]
    ON #IgnoreDuplicateTest
    ( [Col1] ASC )
    WITH (IGNORE_DUP_KEY = ON);

INSERT INTO #IgnoreDuplicateTest (Col1) VALUES (1);
-- (1 row(s) affected)

INSERT INTO #IgnoreDuplicateTest (Col1) VALUES (1);
-- Duplicate key was ignored.
-- (0 row(s) affected)

INSERT INTO #IgnoreDuplicateTest (Col1)
 SELECT tmp.val
 FROM (VALUES (1),(2)) AS tmp(val);
-- Duplicate key was ignored.
-- (1 row(s) affected)

SELECT * FROM #IgnoreDuplicateTest;

-- Col1
--    1
--    2

Answer 2

this may helps you

Here I am checking the values present(which are in variables) are present in table or not, if present leave else insert

 Declare @claimid int, @subscriber int, @qualifyinginformation int
set @claimid = '1000008'
set @subscriber = '1'
set @qualifyinginformation = '1'

If Exists (Select * from test1 where claimid = @claimid and subscriber=@subscriber and qualifyinginformation=@qualifyinginformation )
begin
     print ('The Value already Exist')
 end
else
 begin
   Insert into test1 (claimid,subscriber,qualifyinginformation) values (@claimid,@subscriber,@qualifyinginformation)
select * from test1
end