SQL: Upsert и получить старые и новые значения

Вы можете, как показано ниже:

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

Шаг 1:

Get in cursor all the data of member 1

Шаг 2:

 While moving through cursor.
        Begin
        select hash, expirydate from items where memberid=2 and hash=member1.hash and expirydate=member1.expirydate

Шаг 3

If above brings any result, do not insert.

       else insert.        

Надеюсь это поможет

Примечание: это не настоящий код. Я предоставляю вам только шаги, на основе которых вы можете написать sql.

На самом деле вам просто нужна вставка. Когда ExpiryYear и Hash совпали, ничего делать не хочется. Вы просто хотите вставить из источника в цель, где эти столбцы не совпадают. Вы можете сделать это с помощью Merge или Insert.

CREATE TABLE YourTable
(
    Oldid INT,
    OldMemberId INT,
    Id INT,
    MemberId INT,
    MemberGuid CHAR(5),
    ExpiryYear CHAR(4),
    Hash CHAR(5)
)   

INSERT INTO YourTable VALUES

(null, null, 1, 1, 'Guid1', '2017', 'Hash1'),
(null, null, 2, 1, 'Guid2', '2018', 'Hash2'),
(null, null, 3, 2, 'Guid3', '2020', 'Hash3'),
(null, null, 4, 2, 'Guid4', '2017', 'Hash1')

DECLARE @SourceMemberID AS INT = 1
DECLARE @TargetMemberID AS INT = 2

MERGE [YourTable] AS t
USING
(
    SELECT * FROM [YourTable]
    WHERE MemberId = @SourceMemberID
) AS s
ON t.Hash = s.Hash AND t.ExpiryYear = s.ExpiryYear AND t.MemberId = @TargetMemberID
WHEN NOT MATCHED THEN
    INSERT(Oldid, OldMemberId, Id, MemberId, MemberGuid, ExpiryYear, Hash) VALUES (s.Id, s.MemberId, (SELECT MAX(Id) + 1 FROM [YourTable]), @TargetMemberID, s.MemberGuid, s.ExpiryYear, s.Hash);

SELECT * FROM YourTable

DROP TABLE YourTable

/* Output:
Oldid   OldMemberId Id  MemberId    MemberGuid  ExpiryYear  Hash
-----------------------------------------------------------------
NULL    NULL        1   1           Guid1       2017        Hash1
NULL    NULL        2   1           Guid2       2018        Hash2
NULL    NULL        3   2           Guid3       2020        Hash3
NULL    NULL        4   2           Guid4       2017        Hash1
2       1           5   2           Guid2       2018        Hash2

Если вы просто хотите выбрать, сделайте следующее

SELECT null AS OldID, null AS OldMemberID, Id, MemberId, MemberGuid, ExpiryYear, Hash FROM YourTable
UNION ALL
SELECT A.Id AS OldID, A.MemberId AS OldMemberID, (SELECT MAX(Id) + 1 FROM YourTable) AS Id, @TargetMemberID AS MemberId, A.MemberGuid, A.ExpiryYear, A.Hash
FROM YourTable A
LEFT JOIN
(
    SELECT * FROM YourTable WHERE MemberId = @TargetMemberID
) B ON A.ExpiryYear = B.ExpiryYear AND A.Hash = B.Hash
WHERE A.MemberId = @SourceMemberID AND B.Id IS NULL