65 lines
2.9 KiB
Transact-SQL
65 lines
2.9 KiB
Transact-SQL
IF OBJECT_ID('stage.sp_MrgPedagogusIKTAdatszolgaltatas') IS NOT NULL BEGIN
|
|
DROP PROCEDURE stage.sp_MrgPedagogusIKTAdatszolgaltatas
|
|
END
|
|
GO
|
|
|
|
CREATE PROCEDURE stage.sp_MrgPedagogusIKTAdatszolgaltatas
|
|
AS
|
|
BEGIN
|
|
MERGE stage.T_PEDAGOGUSIKT_STAGE AS trg
|
|
USING (
|
|
SELECT
|
|
PedagogusIKT.C_INTEZMENYID AS C_INTEZMENYID
|
|
,tanev.C_NEV AS C_TANEVNEV
|
|
,PedagogusIKT.ID AS C_PEDAGOGUSIKTID
|
|
,PedagogusIKT.C_ELSODLEGESIKTESZKOZE AS C_ELSODLEGESIKTESZKOZE
|
|
,PedagogusIKT.C_IKTESZKOZHASZNALATMODJA AS C_IKTESZKOZHASZNALATMODJA
|
|
,PedagogusIKT.C_IKTKOMPETENCIASZINTJE AS C_IKTKOMPETENCIASZINTJE
|
|
,PedagogusIKT.C_ALKALMAZOTTID AS C_ALKALMAZOTTID
|
|
FROM dbo.T_PEDAGOGUSIKTADATSZOLGALTATAS PedagogusIKT
|
|
INNER JOIN dbo.T_TANEV tanev ON tanev.ID = PedagogusIKT.C_TANEVID
|
|
WHERE PedagogusIKT.TOROLT = 'F'
|
|
) src ON (trg.C_INTEZMENYID = src.C_INTEZMENYID AND trg.C_PEDAGOGUSIKTID = src.C_PEDAGOGUSIKTID)
|
|
WHEN MATCHED AND
|
|
(trg.C_ELSODLEGESIKTESZKOZE <> src.C_ELSODLEGESIKTESZKOZE OR (trg.C_ELSODLEGESIKTESZKOZE IS NULL AND src.C_ELSODLEGESIKTESZKOZE IS NOT NULL) OR (trg.C_ELSODLEGESIKTESZKOZE IS NOT NULL AND src.C_ELSODLEGESIKTESZKOZE IS NULL))
|
|
OR (trg.C_IKTESZKOZHASZNALATMODJA <> src.C_IKTESZKOZHASZNALATMODJA OR (trg.C_IKTESZKOZHASZNALATMODJA IS NULL AND src.C_IKTESZKOZHASZNALATMODJA IS NOT NULL) OR (trg.C_IKTESZKOZHASZNALATMODJA IS NOT NULL AND src.C_IKTESZKOZHASZNALATMODJA IS NULL))
|
|
OR (trg.C_IKTKOMPETENCIASZINTJE <> src.C_IKTKOMPETENCIASZINTJE OR (trg.C_IKTKOMPETENCIASZINTJE IS NULL AND src.C_IKTKOMPETENCIASZINTJE IS NOT NULL) OR (trg.C_IKTKOMPETENCIASZINTJE IS NOT NULL AND src.C_IKTKOMPETENCIASZINTJE IS NULL))
|
|
OR (trg.C_ALKALMAZOTTID <> src.C_ALKALMAZOTTID OR (trg.C_ALKALMAZOTTID IS NULL AND src.C_ALKALMAZOTTID IS NOT NULL) OR (trg.C_ALKALMAZOTTID IS NOT NULL AND src.C_ALKALMAZOTTID IS NULL))
|
|
THEN
|
|
UPDATE SET
|
|
trg.C_ELSODLEGESIKTESZKOZE = src.C_ELSODLEGESIKTESZKOZE
|
|
,trg.C_IKTESZKOZHASZNALATMODJA = src.C_IKTESZKOZHASZNALATMODJA
|
|
,trg.C_IKTKOMPETENCIASZINTJE = src.C_IKTKOMPETENCIASZINTJE
|
|
,trg.C_ALKALMAZOTTID = src.C_ALKALMAZOTTID
|
|
,trg.LASTCHANGED = GETDATE()
|
|
WHEN NOT MATCHED BY TARGET THEN
|
|
INSERT (
|
|
C_INTEZMENYID
|
|
,C_TANEVNEV
|
|
,C_PEDAGOGUSIKTID
|
|
,C_ELSODLEGESIKTESZKOZE
|
|
,C_IKTESZKOZHASZNALATMODJA
|
|
,C_IKTKOMPETENCIASZINTJE
|
|
,C_ALKALMAZOTTID
|
|
,TOROLT
|
|
,LASTCHANGED
|
|
,CREATED
|
|
) VALUES (
|
|
src.C_INTEZMENYID
|
|
,src.C_TANEVNEV
|
|
,src.C_PEDAGOGUSIKTID
|
|
,src.C_ELSODLEGESIKTESZKOZE
|
|
,src.C_IKTESZKOZHASZNALATMODJA
|
|
,src.C_IKTKOMPETENCIASZINTJE
|
|
,src.C_ALKALMAZOTTID
|
|
,'F'
|
|
,GETDATE()
|
|
,GETDATE()
|
|
)
|
|
WHEN NOT MATCHED BY SOURCE AND trg.TOROLT = 'F' THEN
|
|
UPDATE SET
|
|
trg.LASTCHANGED = GETDATE(),
|
|
trg.TOROLT = 'T';
|
|
END
|
|
GO
|