CREATE PROCEDURE dbo.ImportEqFiles 
/**********************************************************************************************************************
 Purpose:
 Given a valid file path and the "LIKE" filter for file names, import the files from the given file path and log the
 processing of each file in the FileLog table (which is created by this proc if it doesn't exist).  Files that have
 already been imported will not be imported again.

 Usage Examples:
 EXEC dbo.ImportEqFiles @pFilePath, @pFilter
 EXEC dbo.ImportEqFiles 'C:\Temp\eq_files', 'Eq_sample_%.csv'
**********************************************************************************************************************/
--===== Define the parameters for this proc
        @pFilePath      VARCHAR(450)
,       @pFileFilter    VARCHAR(450)
     AS
--=====================================================================================================================
--      Presets
--=====================================================================================================================
--===== Setup the environment
    SET NOCOUNT ON; --Prevents false error returns to the calling procedure or GUI
    SET XACT_ABORT ON; --Short circuits any explicit transactions (if they exist) when an error occurs.

--===== Conditionally drop the all Temp Tables to make reruns in SSMS easier
     IF OBJECT_ID('tempdb..#eq_Staging','U') IS NOT NULL DROP TABLE #eq_Staging;
     IF OBJECT_ID('tempdb..#Directory' ,'U') IS NOT NULL DROP TABLE #Directory;

--===== Create the staging table using all VARCHAR datatypes because
     -- of all the double quotes in the file(s) we're loading.
 CREATE TABLE #eq_Staging
(
        cGroupID            VARCHAR(200)
,       cGroupIDBillable    VARCHAR(200)
,       cGroupName          VARCHAR(200)
,       cGroupDescription   VARCHAR(200)
,       cRowID              VARCHAR(200)
,       cRowName            VARCHAR(200)
,       cRowDescription     VARCHAR(200)
,       cTransactDate       VARCHAR(200)
,       cTransactType       VARCHAR(200)
,       cChargeAccountID    VARCHAR(200)
,       cChargeAccountType  VARCHAR(200)
,       cUserWhoPrinted     VARCHAR(200)
,       cDocumentName      NVARCHAR(200)
,       cUnits              VARCHAR(200)
,       cDocumentID         VARCHAR(200)
,       cDeviceIP           VARCHAR(200)
,       cDevModel           VARCHAR(200)
,       cDevManuf           VARCHAR(200)
,       cDuration           VARCHAR(200)
,       cDestination        VARCHAR(200)
,       cFullName           VARCHAR(200)
,       cPrimaryPIN         VARCHAR(200)
,       cWorkstation        VARCHAR(200)
,       cAmount             VARCHAR(200)
,       JobProperties       VARCHAR(200)
,       cAltCost            VARCHAR(200)
,       cDepartment         VARCHAR(200)
,       cBillable           VARCHAR(200)
,       cAlternatePin       VARCHAR(200)
)
;
--===== Create the table to hold the directory information we'll need.
 CREATE TABLE #Directory
(
        ObjectName  VARCHAR(450)
,       Depth       TINYINT
,       IsFile      TINYINT
)
;
--===== If the file processing log table doesn't already exist, create it now.
     IF OBJECT_ID('dbo.FileLog','U') IS NULL
 CREATE TABLE dbo.FileLog
(
        FileLogID       INT             IDENTITY(1,1)
,       FilePath        VARCHAR(450)    NOT NULL
,       FileName        VARCHAR(450)    NOT NULL
,       StatusDate      DATETIME        NOT NULL DEFAULT GETDATE()
,       FileStatus      VARCHAR(20)     NOT NULL
,       ProcessedBy     VARCHAR(128)    NOT NULL DEFAULT ORIGINAL_LOGIN()
,       CONSTRAINT PK_FileLog PRIMARY KEY CLUSTERED (FileLogID)
,       CONSTRAINT AK_FileLog UNIQUE (FilePath,FileName)
)
;
--===== Declare local variables
DECLARE @FullFileName   VARCHAR(500)
,       @SQL            VARCHAR(MAX)
;
--===== Ensure the file path ends with a backslash and build the quoted file name
 SELECT @pFilePath = CASE WHEN RIGHT(@pFilePath,1) = '\' THEN @pFilePath ELSE @pFilePath + '\' END
;
--===================================================================================================================== 
--      Load all of the filenames from the given path into the log
--===================================================================================================================== 
--===== Load all the file names using xp_dirtree which does NOT require xp_CmdShell to be enabled.
 INSERT INTO #Directory
        (ObjectName, Depth, IsFile)
   EXEC xp_dirtree @pFilePath,1,1
;
--===== Add the files that aren't already in the log as "Import In Process" files that need to be processed.
 INSERT INTO dbo.FileLog
        (FilePath, FileName, StatusDate, FileStatus)
 SELECT FilePath = @pFilePath, ObjectName, StatusDate = GETDATE(), FileStatus = 'Import inprocess'
   FROM #Directory
  WHERE IsFile = 1
    AND ObjectName LIKE @pFileFilter
 EXCEPT
 SELECT FilePath, FileName, StatusDate = GETDATE(), FileStatus = 'Import inprocess'
   FROM dbo.FileLog
;
--===================================================================================================================== 
--      Import the "Import inprocess" files to the staging table
--===================================================================================================================== 
 SELECT @SQL = ISNULL(@SQL,'') + '
   BULK INSERT #eq_Staging
   FROM ' + QUOTENAME(@pFilePath+FileName,'''') + '
   WITH (
        FIELDTERMINATOR = '',''
,       FIRSTROW = 2
,       ROWTERMINATOR = ''''
,       CODEPAGE = ''ACP''
,       DATAFILETYPE = ''widechar''
        );

 UPDATE dbo.FileLog
    SET StatusDate = GETDATE()
,       FileStatus = ''Staged''
  WHERE FilePath = ' + QUOTENAME(@pFilePath,'''') + '
    AND FileName = ' + QUOTENAME(FileName  ,'''') + '
'
   FROM dbo.FileLog
  WHERE FilePath    = @pFilePath
    AND FileName LIKE @pFileFilter
    AND FileStatus  = 'Import inprocess'
;
--===== Display the commands that we're goig to run.
     -- (Note that will be truncated for display at 8K but will be the full command in reality)
  PRINT @SQL

--===== Import and log all the files identified by this run
   EXEC (@SQL)

--===== Todo... this is where you would delete the quotes, validate the data in the staging table, do any error
     -- reporting, and send the data from the staging table to the final table.  Just for demonstration purposes,
     -- we'll simply display the contents of the #eq_Staging table and the dbo.FileLog table.  Keep in mind that a
     -- second run won't import files that have already been logged in the dbo.FileLog table.
 SELECT * FROM #eq_Staging
 SELECT * FROM dbo.FileLog
GO

-----------------------------------------------------------------------------------------------------------------------
--===== Demonstrate the proc.  Since this is the first run, you'll have output from the staging table for the files
     -- that were imported.
   EXEC dbo.ImportEqFiles 'C:\Temp\eq_files', 'Eq_sample_%.csv'
;
--===== Let's run the proc again with the same settings.  Since we've already logged that these files have been 
     -- imported to the staging table and, theoretically processed already, they won't be loaded/displayed again
     -- for this run.  Only the contents of the FileLog table will be displayed.  Of course, if you look at the
     -- "Todo" section in the proc, you have a bit more work to do that I'm going to let you do.  You may also
     -- have to play with the correct code page to get the document names to come out correctly.
   EXEC dbo.ImportEqFiles 'C:\Temp\eq_files', 'Eq_sample_%.csv'

--===== If you want to see it all work as described above again, run the following commented out code.
     -- DROP TABLE dbo.FileLog