sql-server - 防止在更改一列时将记录复制回源表的多次执行中出现重复

Question

我想从 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;

这就是查询的作用:

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

Answer 1

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

第二个问题是如何处理操作尝试插入重复项的情况。您有两个主要选择:

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

2. 您可以将 UNIQUE INDEX 设置为“忽略”重复项，在这种情况下，您无需首先检查，因为操作将默默失败，仅发出未插入重复项的警告。

<小时/>

如果您选择选项#1(首先选中)，则:

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

然后:

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);

如果您选择选项 #2(不选中)，则:

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

然后:

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

<小时/>

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