Upsert - Server 2005

Question

我有两张桌子：

1. NewNotes
2. MasterNotes

每个月，NewNotes表都会更新新帐户和现有帐户的新注释。我需要更新MasterNotes表中的现有注释，或者如果帐户号尚未在MasterNotes表中，则需要插入新注释。

这两个表都有三列：REPORT、ACCOUNT_NUMBER、NOTES。

要更新记录，帐户号和报表必须是相同的( MasterNotes表可以有两个不同的帐户号和两个不同的报告)。

有人能帮忙吗？

到目前为止我的想法是：

IF NOT EXISTS
(
SELECT REP, ACCOUNT_NUMBER
FROM MasterNotes
WHERE REP = (SELECT Report FROM NewNotes) AND
      ACCOUNT_NUMBER = (SELECT AccountNo FROM NewNotes)
)
INSERT INTO MasterNotes
ELSE
UPDATE

我知道这是不正确的，因为子查询返回多个值，但我注意到如何逐行执行。

或者类似的东西？

INSERT INTO MasterNotes
SELECT Report, AccountNo, A.Notes
FROM NewNotes A 
LEFT OUTER JOIN MasterNotes B
    ON A.Report = B.REP AND A.AccountNo = B.ACCOUNT_NUMBER
WHERE B.ACCOUNT_NUMBER IS NULL AND B.REP IS NULL
UPDATE MasterNotes
SET NOTE = B.Notes
FROM MasterNotes A, NewNotes B
WHERE A.ACCOUNT_NUMBER = B.AccountNo AND A.REP = B.Report

Answer 1

这是一些样品让你去。我提供了两种在SQL2005中执行这两种操作的方法。我个人更喜欢JOIN方法，但它们被证明稍慢--但我发现它们更易于编写/读取。它们都在里面，但是被评论掉了。

DECLARE @NewNotes TABLE (REPORT VARCHAR(10), ACCOUNT_NUMBER INT, NOTES VARCHAR(50))
DECLARE @MasterNotes TABLE (REPORT VARCHAR(10), ACCOUNT_NUMBER INT, NOTES VARCHAR(50))

INSERT INTO @MasterNotes (REPORT, ACCOUNT_NUMBER, NOTES)
SELECT 'ABC', 123, 'CUSTOMER IS VERY NICE, SOMETIMES...' UNION
SELECT 'DEF', 456, 'CUSTOMER IS ANGRY'

INSERT INTO @NewNotes (REPORT, ACCOUNT_NUMBER, NOTES)
SELECT 'ABC', 123, 'CUSTOMER IS VERY NICE' UNION
SELECT 'DEF', 456, 'CUSTOMER IS ANGRY' UNION
SELECT 'GHI', 789, 'CUSTOMER WANTS CALL BACK'

UPDATE M SET NOTES = N.NOTES
FROM @MasterNotes M, @NewNotes N
WHERE M.REPORT = N.REPORT
AND M.ACCOUNT_NUMBER = N.ACCOUNT_NUMBER

/** ALTERNATE UPDATE METHOD **/
--UPDATE M SET NOTES = N.NOTES
--FROM @MasterNotes M
--JOIN @NewNotes N
--   ON M.REPORT = N.REPORT
--   AND M.ACCOUNT_NUMBER = N.ACCOUNT_NUMBER

INSERT INTO @MasterNotes (REPORT, ACCOUNT_NUMBER, NOTES)
SELECT N.REPORT, N.ACCOUNT_NUMBER, N.NOTES
FROM @NewNotes N
WHERE NOT EXISTS (SELECT ACCOUNT_NUMBER FROM @MasterNotes M WHERE M.REPORT = N.REPORT)  

/** ALTERNATE INSERT METHOD **/
--INSERT INTO @MasterNotes (REPORT, ACCOUNT_NUMBER, NOTES)
--SELECT  N.REPORT, N.ACCOUNT_NUMBER, N.NOTES
--FROM @NewNotes N
--LEFT OUTER JOIN @MasterNotes M 
--   ON N.REPORT = M.REPORT
--   AND N.ACCOUNT_NUMBER = M.ACCOUNT_NUMBER
--WHERE M.ACCOUNT_NUMBER IS NULL   

SELECT *
FROM @MasterNotes

注意:如果您更新到SQL2008+，您可以使用MERGE函数如下：

DECLARE @NewNotes TABLE (REPORT VARCHAR(10), ACCOUNT_NUMBER INT, NOTES VARCHAR(50))
DECLARE @MasterNotes TABLE (REPORT VARCHAR(10), ACCOUNT_NUMBER INT, NOTES VARCHAR(50))

INSERT INTO @MasterNotes (REPORT, ACCOUNT_NUMBER, NOTES)
SELECT 'ABC', 123, 'CUSTOMER IS VERY NICE, SOMETIMES...' UNION
SELECT 'DEF', 456, 'CUSTOMER IS ANGRY'

INSERT INTO @NewNotes (REPORT, ACCOUNT_NUMBER, NOTES)
SELECT 'ABC', 123, 'CUSTOMER IS VERY NICE' UNION
SELECT 'DEF', 456, 'CUSTOMER IS ANGRY' UNION
SELECT 'GHI', 789, 'CUSTOMER WANTS CALL BACK'

MERGE INTO @MasterNotes AS DST
USING (SELECT REPORT, ACCOUNT_NUMBER, NOTES FROM @NewNotes) AS SRC
    ON SRC.ACCOUNT_NUMBER = DST.ACCOUNT_NUMBER
WHEN MATCHED THEN UPDATE
    SET DST.REPORT = SRC.REPORT,
        DST.NOTES = SRC.NOTES
WHEN NOT MATCHED THEN 
    INSERT (REPORT, ACCOUNT_NUMBER, NOTES)
    VALUES (SRC.REPORT, SRC.ACCOUNT_NUMBER, SRC.NOTES);

SELECT *
FROM @MasterNotes