Bulk Insert Into SQL Table from a folder

--BULK INSERT MULTIPLE FILES From a Folder 
--a table to loop thru filenames drop table ALLFILENAMES
DECLARE @ALLFILENAMES TABLE (
	WHICHPATH VARCHAR(255)
	, WHICHFILE VARCHAR(255)
	)

--TRUNCATE TABLE ALLFILENAMES

--some variables 
DECLARE @filename VARCHAR(255)
	, @path VARCHAR(255)
	, @sql VARCHAR(8000)
	, @cmd VARCHAR(1000)

--get the list of files to process:
-- The path is server specific, e.g. PAWHERSCAPE C Drive, etc
SET @path = 'C:\DUMP\'
SET @cmd = 'dir ' + @path + '*.csv /b'

--select @cmd
INSERT INTO @ALLFILENAMES (WHICHFILE)
EXEC Master..xp_cmdShell @cmd

--select @cmd
UPDATE @ALLFILENAMES
SET WHICHPATH = @path
WHERE WHICHPATH IS NULL

--SELECT * FROM @ALLFILENAMES
--cursor loop
DECLARE c1 CURSOR
FOR
SELECT WHICHPATH
	, WHICHFILE
FROM @ALLFILENAMES
WHERE WHICHFILE LIKE '%.csv%'

OPEN c1

FETCH NEXT
FROM c1
INTO @path
	, @filename

WHILE @@fetch_status <> - 1
BEGIN
	--bulk insert won't take a variable name, so make a sql and execute it instead:
	--First Row skips headers if using firstrow = 2
	-- Replace source table with required
	SET @sql = 'BULK INSERT [EDW].[dbo].[stg_ds_ftclfp_710_extract] FROM ''' + @path + @filename + ''' ' + '     WITH ( 
                   FIELDTERMINATOR = '','', 
                   ROWTERMINATOR = ''\n'', 
                   FIRSTROW = 1 
                ) '

	PRINT @sql

	EXEC (@sql)

	FETCH NEXT
	FROM c1
	INTO @path
		, @filename
END

CLOSE c1

DEALLOCATE c1