Upsert - SQL Server 2005

Question

I have two tables:

1. NewNotes
2. MasterNotes

Every month the NewNotes table is updated with new notes for both new and existing accounts. I need to either update existing notes in the MasterNotes table or insert new notes if the account number isn't yet in the MasterNotes table.

Both tables have three columns: REPORT, ACCOUNT_NUMBER, NOTES.

To update a record both the account number and report have to be the same (the MasterNotes table can have two different account numbers with two different reports).

Can anybody help with this?

Here's my thinking thus far:

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

I know that's not correct because the subquery returns more than one value, but I'm note sure how to go about it row by row.

Or maybe something like this?

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

Here are some samples to get you going. I've provided two ways of doing both actions in SQL2005. I prefer the JOIN methods personally, but they have been shown to be slightly slower -- but I find them easier to write/read. They are within, but commented out.

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

NB: Should you ever update to SQL2008+, you can use the MERGE function as follows:

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