1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58
| --MAJ DES GROUPES DE SAISONS
DECLARE @INSERTED TABLE (
SOURCE_ID INT,
TARGET_ID INT);
INSERT INTO @INSERTED
SELECT SOURCE_ID, TARGET_ID
FROM (
--MERGING DES GROUPES DE SAISONS
MERGE S_PROMO.T_SEASON_GROUP_SGP AS TARGET
USING (SELECT DISTINCT LS.SGP_ID, LS.PEO_VALUE, LS.ISNEW, PEO.PEO_ID
FROM @LIST_SEASONS LS
INNER JOIN S_PROMO.T_PERCENTAGE_OWN_PEO PEO
ON PEO.DTO_ID = @DTO_ID
AND PEO.PEO_PERCENTAGE = LS.PEO_VALUE) AS SOURCE (SGP_ID, PEO_VALUE, ISNEW, PEO_ID)
ON (TARGET.SGP_ID = SOURCE.SGP_ID AND SOURCE.ISNEW = 0)
WHEN MATCHED THEN
UPDATE SET PEO_ID = SOURCE.PEO_ID
WHEN NOT MATCHED THEN
INSERT (PEO_ID) VALUES(SOURCE.PEO_ID)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
OUTPUT INSERTED.SGP_ID, SOURCE.SGP_ID, $ACTION) AS RqMERGE(TARGET_ID, SOURCE_ID, ACTIONS)
WHERE ACTIONS = 'INSERTED';
--MERGING DES COMMENTAIRES DES GROUPES DE SAISONS
MERGE S_PROMO.T_SGP_COMMENT_SGC AS TARGET
USING (SELECT DISTINCT INS.TARGET_ID, LS.COMMENT
FROM @LIST_SEASONS LS
INNER JOIN @INSERTED INS
ON LS.SGP_ID = INS.SOURCE_ID
WHERE LS.ISNEW = 1
UNION
SELECT DISTINCT LS.SGP_ID, LS.COMMENT
FROM @LIST_SEASONS LS
WHERE LS.ISNEW = 0) AS SOURCE (SGP_ID, COMMENT)
ON TARGET.SGP_ID = SOURCE.SGP_ID
WHEN MATCHED THEN
UPDATE SET SGC_COMMENT = SOURCE.COMMENT
WHEN NOT MATCHED THEN
INSERT(SGP_ID, SGC_COMMENT) VALUES (SOURCE.SGP_ID, SOURCE.COMMENT);
--MERGING DES REMARQUES DES GROUPES DE SAISONS
MERGE S_PROMO.T_SGP_NOTE_SGN AS TARGET
USING (SELECT DISTINCT INS.TARGET_ID, LS.NOTE
FROM @LIST_SEASONS LS
INNER JOIN @INSERTED INS
ON LS.SGP_ID = INS.SOURCE_ID
WHERE LS.ISNEW = 1
UNION
SELECT DISTINCT LS.SGP_ID, LS.NOTE
FROM @LIST_SEASONS LS
WHERE LS.ISNEW = 0) AS SOURCE (SGP_ID, NOTE)
ON TARGET.SGP_ID = SOURCE.SGP_ID
WHEN MATCHED THEN
UPDATE SET SGN_NOTE = SOURCE.NOTE
WHEN NOT MATCHED THEN
INSERT(SGP_ID, SGN_NOTE) VALUES (SOURCE.SGP_ID, SOURCE.NOTE); |
Partager