-- COPYRIGHT ©2018 - SQL CODE MONKEY --

-- www.sqlcodemonkey.com --

-- Steven Tidwell --

-- DECLARE SOME VARIABLES --

DECLARE

       @Loop INT, -- NUMBER OF FILES WE WILL BE IMPORTING

       @Count INT = 1, -- DEFAULT OF 1 - THAT WAY WE ALWAYS START AT 1

       @TableName VARCHAR(15), -- THIS WILL HOLD THE NAME OF THE TABLE -- YOU CAN EXPAND THE SIZE

       @FileName VARCHAR(15), -- THIS IS THE FILE NAME THAT WE WILL BE READING FROM THE DIRECTORY

       @DynamicSQL NVARCHAR(MAX) -- THIS WILL HOLD THE SQL CODE THAT WILL DO ALL THE HEAVY LIFTING





-- STEP 1: SCAN THE DIRECTORY AND FIND A LIST OF CSV FILES --
-- CREATE A TEMP TABLE TO HOLD THE FILE NAMES --

IF OBJECT_ID('tempdb.dbo.#Files') IS NOT NULL
       BEGIN
              DROP TABLE #Files
       END

CREATE TABLE #Files
       (
              FileID INT IDENTITY(1,1) NOT NULL, fName NVARCHAR(100), Depts INT, F1 INT
       )


-- NOW WE WILL MAKE A DUPLICATE OF THE ABOVE TABLE IN ORDER TO CLEAN THE FILES AND CREATE THE TABLE NAMES --

IF OBJECT_ID('tempdb.dbo.#CleanFiles') IS NOT NULL
       BEGIN
              DROP TABLE #CleanFiles
       END

CREATE TABLE #CleanFiles
       (
            FileID INT IDENTITY(1,1) NOT NULL, TableName NVARCHAR(100), fName NVARCHAR(100)
       )

-- NOW WE WILL LOOK IN THE DIRECTORY AND GRAB ANY FILES THAT ARE IN THE DIRECTORY --

INSERT INTO #Files
       (fName, Depts, F1)
EXEC master.dbo.xp_DirTree 'H:\DataFilesToUpload',1,1

-- NOW THAT WE HAVE THE FILES IN a TEMP TABLE -- WE CAN STRIP OUT THE NON CSV FILES AND PUSH THEM INTO THE CLEANFILE TABLE --

INSERT INTO #CleanFiles
      (TableName, fName)
SELECT
      LEFT(fName,CHARINDEX('.',fName)-1), fName
FROM #Files WHERE fName LIKE ('%.csv')
SET @Loop = @@ROWCOUNT

-- STEP 2: RUN A LOOP AND IMPORT THE FILES INTO A TEMP TABLE --
WHILE @Loop > 0 AND @Count <= @Loop
       BEGIN
             -- WE WILL SET THE @TableName & @FileName VARIABLES --
              SELECT
                @TableName = cf.TableName,
                @FileName = cf.fName
              FROM
                #CleanFiles cf
              WHERE
                cf.FileID = @Count
-- NOW WE WILL START ADDING THE CODE TO DYNAMIC SQL IN ORDER TO PROCESS THE FILES --

SET @DynamicSQL = N'
-- WE WILL CREATE A TEMP TABLE TO HOLD THE DATA IN EACH FILE --
IF OBJECT_ID(''tempdb.dbo.#' + @TableName + ''') IS NOT NULL
      BEGIN
             DROP TABLE #' + @TableName + '
      END

-- NOW WE WILL CREATE THE TEMP TABLE TO HOLD THE DATA --

-- (YOU SHOULD ALREADY KNOW THE STRUCTURE OF THE DATA SO YOU CAN BUILD THE TABLE TO MATCH THE CSV FILE) --

CREATE TABLE #' + @TableName +'  -- YOU CAN CREATE THIS AS A GLOBAL TEMP TABLE IF YOU WOULD LIKE TO DO THE REST OF THE DATA MANIPULATION IN SQL --
       (
              StockItemName NVARCHAR(200),
              UnitPrice MONEY,
              OnHand INT,
              OrderAmt INT
       )

-- NOW WE WILL LOAD THE DATA INTO THE TEMP TABLE --

BULK INSERT #' + @TableName + '  -- USING DYNAMIC SQL WILL ALLOW US TO LOOP FOR EACH FILE IN OUR TABLE --
       FROM ''H:\DataFilesToUpload\' + @FileName + '''
             WITH (FORMATFILE = ''H:\DataFilesToUpload\InvFormat.xml'')
-- NOW THE DATA FROM THE CSV FILE HAS BEEN LOADED INTO A TEMP TABLE --
-- WE WILL NOW CHECK TO SEE IF THERE IS A PERMANENT TABLE WITH THE SAME NAME ALREADY BUILT -- IF NOT WE WILL BUILD IF -



IF NOT EXISTS(SELECT 1 FROM sys.Tables WHERE [Name] = ''' + @TableName + ''')

       BEGIN

              CREATE TABLE ' + @TableName + '

                     (

                     StockItemName NVARCHAR(200),

                     UnitPrice MONEY,

                     OnHand INT,

                     OrderAmt INT,

                     DateAdded SMALLDATETIME

                     )

-- NOW THAT THE PERMANENT TABLE HAS BEEN BUILT - WE WILL INSERT INTO IT --

       INSERT INTO ' + @TableName + '

       (StockItemName, UnitPrice, OnHand, OrderAmt, DateAdded)

       SELECT

              StockItemName, UnitPrice, OnHand, OrderAmt, GETDATE()

       FROM

              #' + @tableName + '

       END



       '

                    

EXEC sp_ExecuteSQL @DynamicSQL

--PRINT @DynamicSQL



       SET @Count = @Count + 1

END