T-SQL: Creating Stored Procedure that gets Path Folder and inserts into File Path

Question

I have a stored procedure called "sp_BulkInsert" that inserts one .csv file into my database, where you specify the full path of the file when you execute it. I am trying to create another stored procedure called "sp_ResultsDump" where you specify the folder path, which then searches the folder for all .csv files, creates a table with file names, then loops through the rows of that table while executing "sp_BulkInsert" for each .csv file in the folder (the names of which are recorded in the previous table).

Here is the code:

--Step 0: Create Stored Procedure
CREATE PROCEDURE sp_ResultsDump
@PathFolder VARCHAR(2000)
AS

--Step 1: Create table of file names
IF EXISTS (SELECT 1 
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_TYPE='BASE TABLE' 
    AND TABLE_NAME='Files')
    DROP TABLE Files
CREATE TABLE Files(FileID INT IDENTITY NOT NULL, FileName VARCHAR(max))
DECLARE @PathExec VARCHAR(1000)
SET @PathExec = "dir '"+@PathFolder+"'.csv /B"
INSERT INTO Files(FileName) EXEC master..xp_cmdshell @PathExec
DELETE Files WHERE FileName IS NULL

--Step 2: Get # of files, declare and initialize iterator
DECLARE @RowCount INT, @I INT
SET @RowCount = (SELECT COUNT(FileName) FROM Files)
SET @I = 1

--Step 3: Loop through the rows of a table and execute sp_ResultsDump for each file
WHILE (@I <= @RowCount)
BEGIN
        DECLARE @FileName VARCHAR(1000)  
        SELECT @FileName = FileName FROM Files WHERE FileID = @I
        SELECT @FileName = @PathFolder+@FileName
        EXEC sp_BulkInsert @FileName
        SET @I = @I  + 1
END

I have confirmed that Steps 1-3 work when I specify the folder (without creating a stored procedure or a dynamic @variable), however storing the @PathFolder seems to be the problem. For example, I want to grab all .csv files from C:\, and each @FileName through the loop will loop through the file names contained in table Files, column FileName.

What I want to do is to be able to execute the following code so that I can get all .csv files in a specified folder and successfully bulk insert them into my database:

EXEC sp_ResultsDump 'c:\'

The reason for this is because the folder path may change later, and I want the user to be able to specify it.

I believe that "SELECT @FileName = @PathFolder+@FileName" is incorrect, and I tried all sorts of combinations of quotation marks and +'s. Steps 1 and 3 both seem to have problems with @PathFolder.

I guess I just need help with my while loop, because I think if my while loop is correct, this should be good.

Any suggestions? Simple syntax error somewhere? Thanks in advance.

Answer 1

I think your problem is with the following SET command

SET @PathExec = "dir '"+@PathFolder+"'.csv /B"

It appears to be mixing the double-quotes and single-quotes. Try changing it to this

SET @PathExec = 'dir "' + @PathFolder + '.csv" /B'

Answer 2

Figured it out. Thanks.