176 lines
10 KiB
Transact-SQL
176 lines
10 KiB
Transact-SQL
IF OBJECT_ID('stage.sp_MrgNebulo') IS NOT NULL BEGIN
|
|
DROP PROCEDURE stage.sp_MrgNebulo
|
|
END
|
|
GO
|
|
|
|
CREATE PROCEDURE stage.sp_MrgNebulo
|
|
AS
|
|
BEGIN
|
|
MERGE stage.T_NEBULO_STAGE AS trg
|
|
USING (
|
|
SELECT
|
|
nebulo.C_INTEZMENYID AS C_INTEZMENYID
|
|
,tanev.C_NEV AS C_TANEVNEV
|
|
,nebulo.ID AS C_NEBULOID
|
|
,nebulo.C_AJTO AS C_AJTO
|
|
,nebulo.C_EDESANYJASZULETESICSALADINEV AS C_EDESANYJASZULETESICSALADINEV
|
|
,nebulo.C_EDESANYJASZULETESIUTONEVE AS C_EDESANYJASZULETESIUTONEVE
|
|
,nebulo.C_ELOTAG AS C_ELOTAG
|
|
,nebulo.C_EMELET AS C_EMELET
|
|
,nebulo.C_ETIKAHITERKOLCSTAN AS C_ETIKAHITERKOLCSTAN
|
|
,nebulo.C_FELVETELSTATUSZA AS C_FELVETELSTATUSZA
|
|
,nebulo.C_HAZSZAM AS C_HAZSZAM
|
|
,nebulo.C_HELYSEGNEV AS C_HELYSEGNEV
|
|
,nebulo.C_IRANYITOSZAM AS C_IRANYITOSZAM
|
|
,nebulo.C_KOZTERULETJELLEGE AS C_KOZTERULETJELLEGE
|
|
,nebulo.C_KOZTERULETNEV AS C_KOZTERULETNEV
|
|
,nebulo.C_NEME AS C_NEME
|
|
,nebulo.C_OKTATASIAZONOSITOSZAMA AS C_OKTATASIAZONOSITOSZAMA
|
|
,nebulo.C_SZULETESICSALADINEVE AS C_SZULETESICSALADINEVE
|
|
,nebulo.C_SZULETESIHELY AS C_SZULETESIHELY
|
|
,nebulo.C_SZULETESIIDO AS C_SZULETESIIDO
|
|
,nebulo.C_SZULETESIORSZAG AS C_SZULETESIORSZAG
|
|
,nebulo.C_SZULETESIUTONEVE AS C_SZULETESIUTONEVE
|
|
,nebulo.C_TAGOZAT AS C_TAGOZAT
|
|
,nebulo.C_TANULOCSALADINEVE AS C_TANULOCSALADINEVE
|
|
,nebulo.C_TANULOUTONEVE AS C_TANULOUTONEVE
|
|
,nebulo.C_ELETVITELSZERUOTTLAKASKORZET AS C_ELETVITELSZERUOTTLAKASKORZET
|
|
,nebulo.C_FELADATELLATASIHELYID AS C_FELADATELLATASIHELYID
|
|
,nebulo.C_BTMLETSZAMSULY AS C_BTMLETSZAMSULY
|
|
,nebulo.C_SNILETSZAMSULY AS C_SNILETSZAMSULY
|
|
FROM dbo.T_NEBULO nebulo
|
|
INNER JOIN dbo.T_TANEV tanev ON tanev.ID = nebulo.C_TANEVID
|
|
WHERE nebulo.TOROLT = 'F'
|
|
) src ON (trg.C_INTEZMENYID = src.C_INTEZMENYID AND trg.C_NEBULOID = src.C_NEBULOID)
|
|
WHEN MATCHED AND
|
|
(trg.C_AJTO <> src.C_AJTO OR (trg.C_AJTO IS NULL AND src.C_AJTO IS NOT NULL) OR (trg.C_AJTO IS NOT NULL AND src.C_AJTO IS NULL))
|
|
OR (trg.C_EDESANYJASZULETESICSALADINEV <> src.C_EDESANYJASZULETESICSALADINEV OR (trg.C_EDESANYJASZULETESICSALADINEV IS NULL AND src.C_EDESANYJASZULETESICSALADINEV IS NOT NULL) OR (trg.C_EDESANYJASZULETESICSALADINEV IS NOT NULL AND src.C_EDESANYJASZULETESICSALADINEV IS NULL))
|
|
OR (trg.C_EDESANYJASZULETESIUTONEVE <> src.C_EDESANYJASZULETESIUTONEVE OR (trg.C_EDESANYJASZULETESIUTONEVE IS NULL AND src.C_EDESANYJASZULETESIUTONEVE IS NOT NULL) OR (trg.C_EDESANYJASZULETESIUTONEVE IS NOT NULL AND src.C_EDESANYJASZULETESIUTONEVE IS NULL))
|
|
OR (trg.C_ELOTAG <> src.C_ELOTAG OR (trg.C_ELOTAG IS NULL AND src.C_ELOTAG IS NOT NULL) OR (trg.C_ELOTAG IS NOT NULL AND src.C_ELOTAG IS NULL))
|
|
OR (trg.C_EMELET <> src.C_EMELET OR (trg.C_EMELET IS NULL AND src.C_EMELET IS NOT NULL) OR (trg.C_EMELET IS NOT NULL AND src.C_EMELET IS NULL))
|
|
OR (trg.C_ETIKAHITERKOLCSTAN <> src.C_ETIKAHITERKOLCSTAN OR (trg.C_ETIKAHITERKOLCSTAN IS NULL AND src.C_ETIKAHITERKOLCSTAN IS NOT NULL) OR (trg.C_ETIKAHITERKOLCSTAN IS NOT NULL AND src.C_ETIKAHITERKOLCSTAN IS NULL))
|
|
OR (trg.C_FELVETELSTATUSZA <> src.C_FELVETELSTATUSZA OR (trg.C_FELVETELSTATUSZA IS NULL AND src.C_FELVETELSTATUSZA IS NOT NULL) OR (trg.C_FELVETELSTATUSZA IS NOT NULL AND src.C_FELVETELSTATUSZA IS NULL))
|
|
OR (trg.C_HAZSZAM <> src.C_HAZSZAM OR (trg.C_HAZSZAM IS NULL AND src.C_HAZSZAM IS NOT NULL) OR (trg.C_HAZSZAM IS NOT NULL AND src.C_HAZSZAM IS NULL))
|
|
OR (trg.C_HELYSEGNEV <> src.C_HELYSEGNEV OR (trg.C_HELYSEGNEV IS NULL AND src.C_HELYSEGNEV IS NOT NULL) OR (trg.C_HELYSEGNEV IS NOT NULL AND src.C_HELYSEGNEV IS NULL))
|
|
OR (trg.C_IRANYITOSZAM <> src.C_IRANYITOSZAM OR (trg.C_IRANYITOSZAM IS NULL AND src.C_IRANYITOSZAM IS NOT NULL) OR (trg.C_IRANYITOSZAM IS NOT NULL AND src.C_IRANYITOSZAM IS NULL))
|
|
OR (trg.C_KOZTERULETJELLEGE <> src.C_KOZTERULETJELLEGE OR (trg.C_KOZTERULETJELLEGE IS NULL AND src.C_KOZTERULETJELLEGE IS NOT NULL) OR (trg.C_KOZTERULETJELLEGE IS NOT NULL AND src.C_KOZTERULETJELLEGE IS NULL))
|
|
OR (trg.C_KOZTERULETNEV <> src.C_KOZTERULETNEV OR (trg.C_KOZTERULETNEV IS NULL AND src.C_KOZTERULETNEV IS NOT NULL) OR (trg.C_KOZTERULETNEV IS NOT NULL AND src.C_KOZTERULETNEV IS NULL))
|
|
OR (trg.C_NEME <> src.C_NEME OR (trg.C_NEME IS NULL AND src.C_NEME IS NOT NULL) OR (trg.C_NEME IS NOT NULL AND src.C_NEME IS NULL))
|
|
OR (trg.C_OKTATASIAZONOSITOSZAMA <> src.C_OKTATASIAZONOSITOSZAMA OR (trg.C_OKTATASIAZONOSITOSZAMA IS NULL AND src.C_OKTATASIAZONOSITOSZAMA IS NOT NULL) OR (trg.C_OKTATASIAZONOSITOSZAMA IS NOT NULL AND src.C_OKTATASIAZONOSITOSZAMA IS NULL))
|
|
OR (trg.C_SZULETESICSALADINEVE <> src.C_SZULETESICSALADINEVE OR (trg.C_SZULETESICSALADINEVE IS NULL AND src.C_SZULETESICSALADINEVE IS NOT NULL) OR (trg.C_SZULETESICSALADINEVE IS NOT NULL AND src.C_SZULETESICSALADINEVE IS NULL))
|
|
OR (trg.C_SZULETESIHELY <> src.C_SZULETESIHELY OR (trg.C_SZULETESIHELY IS NULL AND src.C_SZULETESIHELY IS NOT NULL) OR (trg.C_SZULETESIHELY IS NOT NULL AND src.C_SZULETESIHELY IS NULL))
|
|
OR (trg.C_SZULETESIIDO <> src.C_SZULETESIIDO OR (trg.C_SZULETESIIDO IS NULL AND src.C_SZULETESIIDO IS NOT NULL) OR (trg.C_SZULETESIIDO IS NOT NULL AND src.C_SZULETESIIDO IS NULL))
|
|
OR (trg.C_SZULETESIORSZAG <> src.C_SZULETESIORSZAG OR (trg.C_SZULETESIORSZAG IS NULL AND src.C_SZULETESIORSZAG IS NOT NULL) OR (trg.C_SZULETESIORSZAG IS NOT NULL AND src.C_SZULETESIORSZAG IS NULL))
|
|
OR (trg.C_SZULETESIUTONEVE <> src.C_SZULETESIUTONEVE OR (trg.C_SZULETESIUTONEVE IS NULL AND src.C_SZULETESIUTONEVE IS NOT NULL) OR (trg.C_SZULETESIUTONEVE IS NOT NULL AND src.C_SZULETESIUTONEVE IS NULL))
|
|
OR (trg.C_TAGOZAT <> src.C_TAGOZAT OR (trg.C_TAGOZAT IS NULL AND src.C_TAGOZAT IS NOT NULL) OR (trg.C_TAGOZAT IS NOT NULL AND src.C_TAGOZAT IS NULL))
|
|
OR (trg.C_TANULOCSALADINEVE <> src.C_TANULOCSALADINEVE OR (trg.C_TANULOCSALADINEVE IS NULL AND src.C_TANULOCSALADINEVE IS NOT NULL) OR (trg.C_TANULOCSALADINEVE IS NOT NULL AND src.C_TANULOCSALADINEVE IS NULL))
|
|
OR (trg.C_TANULOUTONEVE <> src.C_TANULOUTONEVE OR (trg.C_TANULOUTONEVE IS NULL AND src.C_TANULOUTONEVE IS NOT NULL) OR (trg.C_TANULOUTONEVE IS NOT NULL AND src.C_TANULOUTONEVE IS NULL))
|
|
OR (trg.C_FELVETELSTATUSZA <> src.C_FELVETELSTATUSZA OR (trg.C_FELVETELSTATUSZA IS NULL AND src.C_FELVETELSTATUSZA IS NOT NULL) OR (trg.C_FELVETELSTATUSZA IS NOT NULL AND src.C_FELVETELSTATUSZA IS NULL))
|
|
OR (trg.C_ELETVITELSZERUOTTLAKASKORZET <> src.C_ELETVITELSZERUOTTLAKASKORZET OR (trg.C_ELETVITELSZERUOTTLAKASKORZET IS NULL AND src.C_ELETVITELSZERUOTTLAKASKORZET IS NOT NULL) OR (trg.C_ELETVITELSZERUOTTLAKASKORZET IS NOT NULL AND src.C_ELETVITELSZERUOTTLAKASKORZET IS NULL))
|
|
OR (trg.C_FELADATELLATASIHELYID <> src.C_FELADATELLATASIHELYID OR (trg.C_FELADATELLATASIHELYID IS NULL AND src.C_FELADATELLATASIHELYID IS NOT NULL) OR (trg.C_FELADATELLATASIHELYID IS NOT NULL AND src.C_FELADATELLATASIHELYID IS NULL))
|
|
OR (trg.C_BTMLETSZAMSULY <> src.C_BTMLETSZAMSULY OR (trg.C_BTMLETSZAMSULY IS NULL AND src.C_BTMLETSZAMSULY IS NOT NULL) OR (trg.C_BTMLETSZAMSULY IS NOT NULL AND src.C_BTMLETSZAMSULY IS NULL))
|
|
OR (trg.C_SNILETSZAMSULY <> src.C_SNILETSZAMSULY OR (trg.C_SNILETSZAMSULY IS NULL AND src.C_SNILETSZAMSULY IS NOT NULL) OR (trg.C_SNILETSZAMSULY IS NOT NULL AND src.C_SNILETSZAMSULY IS NULL))
|
|
THEN
|
|
UPDATE SET
|
|
trg.C_AJTO = src.C_AJTO
|
|
,trg.C_EDESANYJASZULETESICSALADINEV = src.C_EDESANYJASZULETESICSALADINEV
|
|
,trg.C_EDESANYJASZULETESIUTONEVE = src.C_EDESANYJASZULETESIUTONEVE
|
|
,trg.C_ELOTAG = src.C_ELOTAG
|
|
,trg.C_EMELET = src.C_EMELET
|
|
,trg.C_ETIKAHITERKOLCSTAN = src.C_ETIKAHITERKOLCSTAN
|
|
,trg.C_FELVETELSTATUSZA = src.C_FELVETELSTATUSZA
|
|
,trg.C_HAZSZAM = src.C_HAZSZAM
|
|
,trg.C_HELYSEGNEV = src.C_HELYSEGNEV
|
|
,trg.C_IRANYITOSZAM = src.C_IRANYITOSZAM
|
|
,trg.C_KOZTERULETJELLEGE = src.C_KOZTERULETJELLEGE
|
|
,trg.C_KOZTERULETNEV = src.C_KOZTERULETNEV
|
|
,trg.C_NEME = src.C_NEME
|
|
,trg.C_OKTATASIAZONOSITOSZAMA = src.C_OKTATASIAZONOSITOSZAMA
|
|
,trg.C_SZULETESICSALADINEVE = src.C_SZULETESICSALADINEVE
|
|
,trg.C_SZULETESIHELY = src.C_SZULETESIHELY
|
|
,trg.C_SZULETESIIDO = src.C_SZULETESIIDO
|
|
,trg.C_SZULETESIORSZAG = src.C_SZULETESIORSZAG
|
|
,trg.C_SZULETESIUTONEVE = src.C_SZULETESIUTONEVE
|
|
,trg.C_TAGOZAT = src.C_TAGOZAT
|
|
,trg.C_TANULOCSALADINEVE = src.C_TANULOCSALADINEVE
|
|
,trg.C_TANULOUTONEVE = src.C_TANULOUTONEVE
|
|
,trg.C_ELETVITELSZERUOTTLAKASKORZET = src.C_ELETVITELSZERUOTTLAKASKORZET
|
|
,trg.C_FELADATELLATASIHELYID = src.C_FELADATELLATASIHELYID
|
|
,trg.C_BTMLETSZAMSULY = src.C_BTMLETSZAMSULY
|
|
,trg.C_SNILETSZAMSULY = src.C_SNILETSZAMSULY
|
|
,trg.LASTCHANGED = GETDATE()
|
|
WHEN NOT MATCHED BY TARGET THEN
|
|
INSERT (
|
|
C_INTEZMENYID
|
|
,C_TANEVNEV
|
|
,C_NEBULOID
|
|
,C_AJTO
|
|
,C_EDESANYJASZULETESICSALADINEV
|
|
,C_EDESANYJASZULETESIUTONEVE
|
|
,C_ELOTAG
|
|
,C_EMELET
|
|
,C_ETIKAHITERKOLCSTAN
|
|
,C_FELVETELSTATUSZA
|
|
,C_HAZSZAM
|
|
,C_HELYSEGNEV
|
|
,C_IRANYITOSZAM
|
|
,C_KOZTERULETJELLEGE
|
|
,C_KOZTERULETNEV
|
|
,C_NEME
|
|
,C_OKTATASIAZONOSITOSZAMA
|
|
,C_SZULETESICSALADINEVE
|
|
,C_SZULETESIHELY
|
|
,C_SZULETESIIDO
|
|
,C_SZULETESIORSZAG
|
|
,C_SZULETESIUTONEVE
|
|
,C_TAGOZAT
|
|
,C_TANULOCSALADINEVE
|
|
,C_TANULOUTONEVE
|
|
,C_ELETVITELSZERUOTTLAKASKORZET
|
|
,C_FELADATELLATASIHELYID
|
|
,C_BTMLETSZAMSULY
|
|
,C_SNILETSZAMSULY
|
|
,TOROLT
|
|
,LASTCHANGED
|
|
,CREATED
|
|
) VALUES (
|
|
src.C_INTEZMENYID
|
|
,src.C_TANEVNEV
|
|
,src.C_NEBULOID
|
|
,src.C_AJTO
|
|
,src.C_EDESANYJASZULETESICSALADINEV
|
|
,src.C_EDESANYJASZULETESIUTONEVE
|
|
,src.C_ELOTAG
|
|
,src.C_EMELET
|
|
,src.C_ETIKAHITERKOLCSTAN
|
|
,src.C_FELVETELSTATUSZA
|
|
,src.C_HAZSZAM
|
|
,src.C_HELYSEGNEV
|
|
,src.C_IRANYITOSZAM
|
|
,src.C_KOZTERULETJELLEGE
|
|
,src.C_KOZTERULETNEV
|
|
,src.C_NEME
|
|
,src.C_OKTATASIAZONOSITOSZAMA
|
|
,src.C_SZULETESICSALADINEVE
|
|
,src.C_SZULETESIHELY
|
|
,src.C_SZULETESIIDO
|
|
,src.C_SZULETESIORSZAG
|
|
,src.C_SZULETESIUTONEVE
|
|
,src.C_TAGOZAT
|
|
,src.C_TANULOCSALADINEVE
|
|
,src.C_TANULOUTONEVE
|
|
,src.C_ELETVITELSZERUOTTLAKASKORZET
|
|
,src.C_FELADATELLATASIHELYID
|
|
,src.C_BTMLETSZAMSULY
|
|
,src.C_SNILETSZAMSULY
|
|
,'F'
|
|
,GETDATE()
|
|
,GETDATE()
|
|
)
|
|
WHEN NOT MATCHED BY SOURCE AND trg.TOROLT = 'F' THEN
|
|
UPDATE SET
|
|
trg.LASTCHANGED = GETDATE(),
|
|
trg.TOROLT = 'T';
|
|
END
|
|
GO
|