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 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77
| BEGIN TRANSACTION maTransaction
DECLARE @errors INT --On déclare une variable qui sera destiné à accueillir nos erreurs
SET @errors = 0
BEGIN
UPDATE INCIDENTS
SET Id_entreprise=E.Id_entreprise,
Id_naf=C.Id_naf,
Id_type=H.Id_type,
Id_geographie=G.Id_geographie,
Id_date=D.PK_Date,
Montant=T.Montant,
Date_info=T.Date_info,
Date_pub=T.Date_pub,
Date_maj=T.Date_maj
FROM INCIDENTS AS I
INNER JOIN TEMP AS T
ON Raison_Sociale = T.Raison_Sociale
AND Siren = T.Siren
INNER JOIN ENTREPRISES AS E
ON T.Raison_Sociale = E.Raison_Sociale
AND T.Siren = E.Siren
INNER JOIN CODE_NAF AS C
ON T.Naf = C.Naf
INNER JOIN GEOGRAPHIE AS G
ON T.Departement = G.Code_departement
INNER JOIN TYPES_INCIDENTS AS H
ON T.Type = H.Type
AND T.Type_codinf = H.Type_codinf
INNER JOIN DimDate AS D
ON T.Date_maj = (SELECT CONVERT(date, D.PK_Date, 112))
SET @errors = @errors + @@ERROR
INSERT INCIDENTS
(
Id_entreprise
, Id_naf
, Id_date
, Id_geographie
, Date_info
, Date_maj
, Date_pub
, Montant
, Id_type
)
SELECT E.Id_entreprise
, C.Id_Naf
, D.PK_Date
, G.Id_geographie
, T.Date_info
, T.Date_maj
, T.Date_pub
, T.Montant
, H.Id_type
FROM TEMP AS T
INNER JOIN ENTREPRISES AS E
ON T.Raison_Sociale = E.Raison_Sociale
AND T.Siren = E.Siren
INNER JOIN CODE_NAF AS C
ON T.Naf = C.Naf
INNER JOIN GEOGRAPHIE AS G
ON T.Departement = G.Code_departement
INNER JOIN TYPES_INCIDENTS AS H
ON T.Type = H.Type
AND T.Type_codinf = H.Type_codinf
INNER JOIN DimDate AS D
ON T.Date_maj = (SELECT CONVERT(date, D.PK_Date, 112))
SET @errors = @errors + @@ERROR
IF @errors = 0 --Si errors est égale à 0, donc s'il n'y a eu aucune erreur
COMMIT TRANSACTION maTransaction -- On commit la transaction
ELSE --S'il y a eu des erreurs
ROLLBACK TRANSACTION maTransaction --On annule tous les changements de cette transaction |
Partager