[SQL Server] Déplacer et renommer des fichiers pdf et xlsx via Transact SQL
-- =============================================
-- Author: KHARROUBI Zouhaier
-- Create date: 26/11/2013
-- Description: Déplacer et renommer les fichiers Facture du \\serveur_1\FACTURE vers \\serveur_2\FACTURE
-- Exemple Utilisation :
/*
DECLARE @return_value int
EXEC @return_value = [dbo].[usp_Copier_FACTURE]
@Chemin_Source = N'\\serveur_1\FACTURE',
@Chemin_Destination = N'\\serveur_2\FACTURE'
SELECT 'Return Value' = @return_value
GO
*/
-- =============================================
ALTER PROCEDURE [dbo].[usp_Copier_FACTURE]
-- Add the parameters for the stored procedure here
@Chemin_Source VARCHAR(300), -- \\serveur_1\FACTURE
@Chemin_Destination VARCHAR(300) -- \\serveur_2\FACTURE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#ListeFichiersFACTURE]'))
DROP TABLE #ListeFichiersFACTUR
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#ListeFichiersfacture_Temp]')
DROP TABLE #ListeFichiersFACTURE_Temp
-- Insert statements for procedure here
CREATE TABLE #ListeFichiersFACTURE
(
Num_Fic_FACTURE_Source INT IDENTITY(1,1),
Fic_FACTURE_Source VARCHAR(60) NULL
)
CREATETABLE#ListeFichiersFACTURE_Temp
(
Num_Fic_FACTURE_Source INTIDENTITY(1,1),
Fic_FACTURE_Source VARCHAR(60)NULL
)
DECLARE @Nbre_Fic_FACTURE_Source AS INT
DECLARE @CommandeDos AS NVARCHAR(MAX)
DECLARE @Extension_FACTURE AS VARCHAR(5)
DECLARE @Position_extension AS TINYINT
DECLARE @Fic_FACTURE_Source AS VARCHAR(60)
DECLARE @Fic_FACTURE_Source_Sans_Ext AS VARCHAR(60)
DECLARE @Fic_FACTURE_Destination AS VARCHAR(60)
DECLARE @Num_Restaurant AS CHAR(3)
DECLARE @Date_FACTURE AS DATE
DECLARE @Annee_Mois_FACTURE AS CHAR(6)
--Construire la chaine YYYYMM : (ANNEEMOIS)
SELECT @Date_FACTURE=CAST(DATEPART(YEAR,GETDATE()) AS CHAR(4))
+ CAST(REPLICATE('0',2- LEN(DATEPART(MONTH,GETDATE()))) AS VARCHAR(2)) + CAST(DATEPART(MONTH,GETDATE()) AS VARCHAR(2))
+ '01'
SELECT @Date_FACTURE=DATEADD(DAY,-1,@Date_FACTURE)
SELECT @Annee_Mois_FACTURE=CAST(DATEPART(YEAR,@Date_FACTURE) AS CHAR(4))
+ CAST(REPLICATE('0',2- LEN(DATEPART(MONTH,@Date_FACTURE))) AS VARCHAR(2)) + CAST(DATEPART(MONTH,@Date_FACTURE) AS VARCHAR(2))
INSERT INTO #ListeFichiersFACTURE EXEC master.sys.xp_cmdshell'DIR /B "\\serveur_1\FACTURE\FACTUREFR*.pdf"'
INSERT INTO #ListeFichiersFACTURE EXEC master.sys.xp_cmdshell'DIR /B "\\serveur_1\FACTURE\FACTUREFR*.xlsx"'
INSERT INTO #ListeFichiersFACTURE EXEC master.sys.xp_cmdshell'DIR /B "\\serveur_1\FACTURE\FACTURETN*.pdf"'
INSERT INTO #ListeFichiersFACTURE EXEC master.sys.xp_cmdshell'DIR /B "\\serveur_1\FACTURE\FACTURETN*.xlsx"'
--Supprimer les fichiers XML
EXEC master.sys.xp_cmdshell'del "\\serveur_1\FACTURE\*.xml /q"'
DELETE FROM #ListeFichiersFACTURE
WHERE Fic_FACTURE_Source IS NULL
INSERT INTO #ListeFichiersFACTURE SELECT Fic_FACTURE_Source FROM #ListeFichiersFACTURE_Temp
SELECT @Nbre_Fic_FACTURE_Source=COUNT(Fic_FACTURE_Source) FROM #ListeFichiersFACTURE
WHILE @Nbre_Fic_FACTURE_Source>0
BEGIN
SELECT @Fic_FACTURE_Source=Fic_FACTURE_Source FROM #ListeFichiersFACTURE WHERE Num_Fic_FACTURE_Source=@Nbre_Fic_FACTURE_Source
--Récuperer l'extension du fichier
SELECT @Extension_FACTURE=SUBSTRING( @Fic_FACTURE_Source , CHARINDEX('.',@Fic_FACTURE_Source) , 5 )
-- position de l'extension ".pdf" ou ".xlsx"
SELECT @position_extension=CHARINDEX(@Extension_FACTURE,@Fic_FACTURE_Source)
--la chaine sans l'extension
SELECT @Fic_FACTURE_Source_Sans_Ext=SUBSTRING( @Fic_FACTURE_Source , 0 , @position_extension )
SELECT @Fic_FACTURE_Destination=LEFT(@Fic_FACTURE_Source_Sans_Ext,5) + RIGHT(@Fic_FACTURE_Source_Sans_Ext,3) + '_' + @Annee_Mois_FACTURE
--copier le FACTURE selectionnée
SELECT @CommandeDos='master.sys.xp_cmdshell ''MOVE /Y ' + @Chemin_Source + '\' + @Fic_FACTURE_Source + ' ' + @Chemin_Destination + '\' + @Fic_FACTURE_Destination + @Extension_FACTURE + ''''
EXEC (@CommandeDos)
SELECT @Nbre_Fic_FACTURE_Source = @Nbre_Fic_FACTURE_Source -1
END
DROP TABLE #ListeFichiersFACTURE
DROP TABLE #ListeFichiersFACTURE_Temp
END