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
|
CREATE PROCEDURE insert_DP_Histolig
AS
DECLARE @DernierMAJ AS datetime
--date de la dernière mise à jour de la table Ligneclient par la table DP_Histolig
SELECT @DernierMAJ=MAX(dat_mvt) from [Base Tampon].dbo.LigneClient where origine='DP'
--déclaration des champs à utiliser dans le datawarehouse
DECLARE @avoir As bit
DECLARE @marge As real
DECLARE @dat_cde As datetime
DECLARE @origine As varchar(2)
DECLARE @typ_ligne As varchar(2)
DECLARE @cod_pro As bigint
DECLARE @dat_mvt As datetime
DECLARE @sous_type As varchar(2)
DECLARE @typ_elem As varchar(3)
DECLARE @quadri_Cde As int
DECLARE @numSem_Cde As int
DECLARE @jour_Cde As int
DECLARE @mois_Cde As int
DECLARE @annee_Cde As int
DECLARE @quadri_Fac As int
DECLARE @numSem_Fac As int
DECLARE @jour_Fac As int
DECLARE @mois_Fac As int
DECLARE @annee_Fac As int
DECLARE @nom_pro As varchar(35)
DECLARE @qte As real
DECLARE @px_vte As real
DECLARE @remise1 As real
DECLARE @remise2 As bigint
DECLARE @groupe As bigint
DECLARE @famille As bigint
DECLARE @s_famille As bigint
DECLARE @px_ach As real
DECLARE @no_cde As bigint
DECLARE @dat_liv As datetime
DECLARE @commerc As varchar(7)
DECLARE @mt_ht As real
DECLARE @qte_cde As bigint
DECLARE @remise3 As bigint
DECLARE curseur cursor
FOR
SELECT avoir,marge,dat_cde,origine,typ_ligne,cod_pro,dat_mvt,sous_type,typ_elem,quadri_Cde,numSem_Cde,jour_Cde,mois_Cde
,annee_Cde,quadri_Fac,numSem_Fac,jour_Fac,mois_Fac,annee_Fac,nom_pro,qte
,px_vte,remise1,remise2,groupe,famille,s_famille,px_ach,no_cde
,dat_liv,commerc,mt_ht,qte_cde,remise3
FROM [Base Tampon].dbo.DP_Histolig
WHERE dat_mvt > @DernierMAJ
OPEN curseur
FETCH curseur INTO @avoir,@marge,@dat_cde,@origine,@typ_ligne,@cod_pro,@dat_mvt,@sous_type,@typ_elem,@quadri_Cde,@numSem_Cde,@jour_Cde,@mois_Cde
,@annee_Cde,@quadri_Fac,@numSem_Fac,@jour_Fac,@mois_Fac,@annee_Fac,@nom_pro,@qte
,@px_vte,@remise1,@remise2,@groupe,@famille,@s_famille,@px_ach,@no_cde
,@dat_liv,@commerc,@mt_ht,@qte_cde,@remise3
WHILE @@fetch_Status = 0
BEGIN
INSERT INTO [Base Tampon].dbo.LigneClient(avoir,marge,dat_cde,origine,typ_ligne,cod_pro,dat_mvt,sous_type,typ_elem,quadri_Cde,numSem_Cde,jour_Cde,mois_Cde
,annee_Cde,quadri_Fac,numSem_Fac,jour_Fac,mois_Fac,annee_Fac,nom_pro,qte
,px_vte,remise1,remise2,groupe,famille,s_famille,px_ach,no_cde
,dat_liv,commerc,mt_ht,qte_cde,remise3)
VALUES (@avoir,@marge,@dat_cde,@origine,@typ_ligne,@cod_pro,@dat_mvt,@sous_type,@typ_elem,@quadri_Cde,@numSem_Cde,@jour_Cde,@mois_Cde
,@annee_Cde,@quadri_Fac,@numSem_Fac,@jour_Fac,@mois_Fac,@annee_Fac,@nom_pro,@qte
,@px_vte,@remise1,@remise2,@groupe,@famille,@s_famille,@px_ach,@no_cde
,@dat_liv,@commerc,@mt_ht,@qte_cde,@remise3)
FETCH curseur INTO @avoir,@marge,@dat_cde,@origine,@typ_ligne,@cod_pro,@dat_mvt,@sous_type,@typ_elem,@quadri_Cde,@numSem_Cde,@jour_Cde,@mois_Cde
,@annee_Cde,@quadri_Fac,@numSem_Fac,@jour_Fac,@mois_Fac,@annee_Fac,@nom_pro,@qte
,@px_vte,@remise1,@remise2,@groupe,@famille,@s_famille,@px_ach,@no_cde
,@dat_liv,@commerc,@mt_ht,@qte_cde,@remise3
END
CLOSE curseur
DEALLOCATE curseur
GO |
Partager