如何在插入之前检查记录是否存在以防止重复？

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.

我想从tblOrder中选择CustomerID，OrderTypeID和LoanNumber，并使用新的OrderTypeID插入它，但是同样的CustomerID和LoanNumber。我的查询执行此操作但它复制了值，每次执行查询时它都会复制行。

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.

OrderID 9 - 12是重复的或OrderID 5 - 8.查询执行了2次，因此记录重复。

The ideal result should be:

理想的结果应该是：

4 个解决方案

#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.

这里的第一个也是最重要的问题不是您的查询插入重复项;这是你的桌子允许它首先发生。因此，您首先需要在这3个字段上创建一个UNIQUE INDEX以禁止重复。

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

   您可以先检查记录是否存在，如果找到则跳过INSERT，或者

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.

   您可以将UNIQUE INDEX设置为“忽略”重复项，在这种情况下，您不需要先检查，因为操作将无提示失败，只是警告未插入重复项。

---

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

如果选择选项＃1（先检查），则：

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:

如果选择选项＃2（不检查），则：

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:

IGNORE_DUP_KEY行为的示例：

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

#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

#3

Is this what you want?

这是你想要的吗？

CREATE TABLE tblOrder(
    OrderID     INT IDENTITY(1, 1),
    CustomerID  INT,
    OrderTypeID INT,
    LoanNumber  INT
)
INSERT INTO tblOrder VALUES
(1, 1, 45584565),
(1, 1, 45566856),
(1, 1, 45565584),
(1, 1, 45588545)

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

#4

Is this what you look for ? Make sure

这是你要找的？确保

Create table #temp(OrderID int, CustomerID int,  OrderTypeID int, LoanNumber int)
INsert into #temp values(1  , 1  , 1  , 45584565)
INsert into #temp values(2 ,  1 ,  1 ,  45566856)
INsert into #temp values(3  , 1  , 1 ,  45565584)
INsert into #temp values(4  , 1  , 1  , 45588545)

select * from #temp



UPDATE TE
SET  CustomerID=tab.CustomerID,OrderTypeID=3,LoanNumber=tab.LoanNumber
FROM #temp TE inner join
(Select o.OrderID,o.CustomerID,3 tt, o.LoanNumber
from #temp as o
where o.OrderTypeID = 1)tab

ON TE.OrderID = tab.OrderID

select * from #temp
drop table #temp