How do I use bulk insert to import a file just based on its file extension?

bulkinsert, import, sql, sql-server

Solution

You'll need dynamic SQL for this.

Assuming that the file names are already in `myFileList`, then this is how I would do it:

DECLARE @sql As VARCHAR(MAX);
SET @sql = '';

SELECT @sql = @sql + REPLACE('
    BULK INSERT Data_MaximusImport_t
    FROM ''C:\Program Files (x86)\DataMaxx\*''
    WITH (FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'' );
    ', '*', myFileName) 
FROM    myFileList
WHERE   myfileName != '';

PRINT @sql;
EXEC(@sql);

Problem

I have a folder that new log files get created every hour. Each time the file name is different. How do I bulk insert just based on any file that has the extension .log? Here is my code ``` select * from [data_MaximusImport_t] BULK INSERT Data_MaximusImport_t FROM 'C:\Program Files (x86)\DataMaxx\*.log' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ) ``` Right now I get the error *.log" could not be opened. Operating system error code 123(The filename, directory name, or volume label syntax is incorrect.). ***this is an edit to my original question. I was able to figure out the file name with this code ``` DECLARE @Path varchar(256) = 'dir C:\datamaxx\*.log' DECLARE @Command varchar(1024) = @Path + ' /A-D /B' INSERT INTO myFileList EXEC MASTER.dbo.xp_cmdshell @Command SELECT * FROM myFileList ``` Now i just need to figure out how to stick that name in the path. SHould i delcare the file name as a variable?

Original source