SQL: Upsert and get the old and the new values

Question

I have the following table Items:

Id          MemberId          MemberGuid        ExpiryYear         Hash    
--------------------------------------------------------------------------- 
1           1                 Guid1             2017               Hash1
2           1                 Guid2             2018               Hash2
3           2                 Guid3             2020               Hash3
4           2                 Guid4             2017               Hash1

I need to copy the items from a member to another (not just to update MemberId, to insert a new record). The rule is: if I want to migrate all the items from a member to another, I will have to check that that item does not exists in the new member.

For example, if I want to move the items from member 1 to member 2, I will move only item with id 2, because I already have an item at member 2 with the same hash and with the same expiry year (this are the columns that I need to check before inserting the new items).

How to write a query that migrates only the non-existing items from a member to another and get the old id and the new id of the records? Somehow with an upsert?

Answer 1

You can as the below:

-- MOCK DATA
DECLARE @Tbl TABLE
(    
    Id INT IDENTITY NOT NULL PRIMARY KEY,
    MemberId INT,
    MemberGuid CHAR(5),
    ExpiryYear CHAR(4),
    Hash CHAR(5)
)   

INSERT INTO @Tbl        
VALUES
(1, 'Guid1', '2017', 'Hash1'),
(1, 'Guid2', '2018', 'Hash1'),
(2, 'Guid3', '2020', 'Hash3'),
(2, 'Guid4', '2017', 'Hash1')
-- MOCK DATA

-- Parameters
DECLARE @FromParam INT = 1 
DECLARE @ToParam INT = 2


DECLARE @TmpTable TABLE (NewDataId INT, OldDataId INT)

MERGE @Tbl AS T
USING
(
    SELECT * FROM @Tbl
    WHERE MemberId = @FromParam
) AS F
ON  T.Hash = F.Hash AND 
    T.ExpiryYear = F.ExpiryYear AND 
    T.MemberId = @ToParam
WHEN NOT MATCHED THEN
    INSERT ( MemberId, MemberGuid, ExpiryYear, Hash) 
    VALUES ( @ToParam, F.MemberGuid, F.ExpiryYear, F.Hash)
    OUTPUT inserted.Id, F.Id INTO @TmpTable;

SELECT * FROM @TmpTable