[SQL Server] Génération d'un script DOS via un Script Transact SQL

  • Imprimer

/*Auteur =   KHARROUBI Zouhaier */

/*Objectif = Génération automatique d'un script dos via Transact SQL*/

/*Date  =    01/02/2014*/

DECLARE @Premiere_Date_Maj    AS DATETIME

DECLARE @Premiere_Date_Maj_Temp     AS DATETIME

DECLARE @Derniere_Date_Maj    AS DATETIME

DECLARE @Nom_SiteVente                AS CHAR(6)

DECLARE @Id_SiteVente       AS INT

DECLARE @Nbre_SiteVente         AS INT

DECLARE @Mois                      AS CHAR(2)

DECLARE @Jour                      AS CHAR(2)

DECLARE @Annee                     AS CHAR(2)

CREATE TABLE #scriptsDos

(

script VARCHAR(500)

)

SELECT @Premiere_Date_Maj=MIN([date_debut])FROM [Site_Ventes_Version]

WHERE code_pays=4 AND version_windows=8

  SELECT f.code_pays

      ,f.num_site_vente

      ,f.Id_SiteVente

      ,f.date_fin

      ,d.date_debut

  INTO #list_Sitevente

  FROM Site_Ventes_Version f

  JOIN Site_Ventes_Version d on d.Id_SiteVente=f.Id_SiteVente and d.date_fin IS NULL

  WHERE f.code_pays=4 and f.date_fin IS NOT NULL

SELECT @Nbre_SiteVente=COUNT(num_site_vente) FROM #list_Sitevente

 WHILE @Nbre_SiteVente>0

       BEGIN

       

            SELECT @Premiere_Date_Maj_Temp=@Premiere_Date_Maj

              SELECT TOP 1 @Nom_SiteVente='MKT' + REPLICATE('0',3-LEN(CAST(num_site_vente AS CHAR(3)))) + CAST(num_site_vente AS CHAR(3))

             ,@Derniere_Date_Maj=date_fin,@Id_SiteVente=Id_SiteVente

             FROM #list_Sitevente ORDER BY date_debut,num_site_vente

     

            WHILE       @Premiere_Date_Maj_Temp<=@Derniere_Date_Maj

                  BEGIN

                        SELECT  @Mois=REPLICATE('0',2-LEN(CAST(DATEPART(MM,@Premiere_Date_Maj_Temp) AS CHAR(2)))) + CAST(DATEPART(MM,@Premiere_Date_Maj_Temp) AS CHAR(2))

                        SELECT  @Jour=REPLICATE('0',2-LEN(CAST(DATEPART(DD,@Premiere_Date_Maj_Temp) AS CHAR(2)))) + CAST(DATEPART(DD,@Premiere_Date_Maj_Temp) AS CHAR(2))

                        SELECT @Annee=RIGHT(DATEPART(YY,@Premiere_Date_Maj_Temp),2)

                        INSERT INTO #scriptsDos(script) VALUES ('COPY A:\SITE_VENTES\ARRIVEE\MEKTABA\' + @Nom_SiteVente + '\' + @Annee + @Mois + @Jour + '*.* B:\SITE_VENTES\BACKUP\MEKTABA\' + @Nom_SiteVente + '\ /y')

                        INSERT INTO #scriptsDos(script) VALUES ('DEL A:\SITE_VENTES\ARRIVEE\MEKTABA\' + @Nom_SiteVente + '\' + @Annee + @Mois + @Jour + '*.* /q')

                        SELECT @Premiere_Date_Maj_Temp=DATEADD(DAY,1,@Premiere_Date_Maj_Temp)

                  END

            DELETE FROM #list_Sitevente WHERE Id_SiteVente=@Id_SiteVente

            SELECT @Nbre_SiteVente=COUNT(num_site_vente) FROM #list_SiteVente

       END

SELECT script FROM #scriptsDos

DROP TABLE  #list_SiteVente

DROP TABLE #scriptsDos