USE T201920

CREATE TABLE Tmp_Brok (BrokSrNo Varchar(255), BrokSuppCode Varchar(255), PaySrNo Varchar(255),PayYear Varchar(255),CompCode Varchar(255))
INSERT INTO Tmp_Brok SELECT BROKERAGE_DETAIL.BrokSrNo, BROKERAGE_DETAIL.BrokSuppCode, BROKERAGE_ADDLESS_DETAIL.PaySrNo, BROKERAGE_ADDLESS_DETAIL.PayYear, BROKERAGE_ADDLESS_DETAIL.CompCode FROM BROKERAGE_DETAIL INNER JOIN BROKERAGE_ADDLESS_DETAIL ON BROKERAGE_DETAIL.BrokSrNo = BROKERAGE_ADDLESS_DETAIL.BrokSrNo
ALTER TABLE Tmp_Brok Add RecSuppCode Varchar(255)
--UPDATE Tmp_Brok SET RecSuppCode = T1.F2 FROM (SELECT RECSRNO AS F1, REcSuppCode AS F2 FROM T201718.DBO.RECEIVED_DETAIL) AS T1 WHERE T1.F1 COLLATE DATABASE_DEFAULT = PaySrNo and PayYear = '2017-18'
UPDATE Tmp_Brok SET RecSuppCode = T1.F2 FROM (SELECT RECSRNO AS F1, REcSuppCode AS F2 FROM T201819.DBO.RECEIVED_DETAIL) AS T1 WHERE T1.F1 COLLATE DATABASE_DEFAULT = PaySrNo and PayYear = '2018-19'
UPDATE Tmp_Brok SET RecSuppCode = T1.F2 FROM (SELECT RECSRNO AS F1, REcSuppCode AS F2 FROM T201920.DBO.RECEIVED_DETAIL) AS T1 WHERE T1.F1 COLLATE DATABASE_DEFAULT = PaySrNo and PayYear = '2019-20'

UPDATE BROKERAGE_DETAIL SET BrokSuppCode = T1.F2 FROM (SELECT BrokSrNo AS F1, RecSuppCode AS F2, CompCode AS F3 FROM Tmp_Brok) AS T1 WHERE T1.F1 COLLATE DATABASE_DEFAULT = BrokSrNo AND T1.F3 COLLATE DATABASE_DEFAULT = CompCode
DROP TABLE Tmp_Brok