Bulk Insert file name Like SQL server

Question

I have three files in my PRODUCTION folder. File names will be concatenated with current Dates. For example PRODFILE1_01052014, PRODFILE2_01052014 etc. Daily files will be placed with current dates. I need to write an SP to BULK INSERT text files daily. I could write below script to bulk insert a particular dated file. How I can pick all the files that contains PRODFILE?

BULK INSERT PRODFILES FROM 'D:\PRODUCTION\PRODFILE1_01052014.TXT'
WITH (FIELDTERMINATOR ='|',ROWTERMINATOR = '\n')

I need something like this Like PRODFILE1*.txt, Like PRODFILE2*.txt

score 1 · Accepted Answer · edited May 23 '17 at 12:27

1

You will need to list all files in the PRODUCTION folder, so that you can filter the files by their names. Take a look here: How to list files inside a folder with SQL Server

edited May 23 '17 at 12:27

Community

1
1

answered May 26 '14 at 11:17

Dan

10,480
23
49

score 0 · Answer 2 · answered Jun 27 '17 at 19:20

So it seems you are trying to automatically look into the PRODUCTION folder and BULK INSERT the latest "today's" files?

I think you are looking for something like this....

-- load up all the files in your PRODUCTION folder into a variable
DECLARE @BackupDirectory SYSNAME = 'C:\PRODUCTION\\'  -- remove the extra slash from the path! This is so the StockOverflow color formatting works properly on the page.
DECLARE @allFiles AS TABLE(fName varchar(500), d INT, f INT)
DECLARE @fileToImport AS VARCHAR(500)
INSERT INTO @allFiles
EXEC master.sys.xp_DirTree @BackupDirectory, 10, 1

-- get today's file suffix i.e.: {01052014} or whatever the date is today
DECLARE @dateSuffix AS CHAR(8)
SET @dateSuffix = CAST(RIGHT(CONCAT('00', DATEPART(dd, getdate())), 2) AS CHAR(2)) 
+ CAST(RIGHT(CONCAT('00', DATEPART(mm, getdate())), 2) AS CHAR(2))
+ CAST(DATEPART(yy, getdate()) AS CHAR(4))
SELECT @dateSuffix

DECLARE @i AS INT
SET @i = 1

-- loop through the files one by one and execute the bulk insert.
WHILE @i <= (SELECT COUNT(*) FROM @allFiles WHERE fName LIKE '%'+@dateSuffix)
BEGIN
   SELECT TOP 1 @fileToImport = fName FROM @allFiles WHERE fName LIKE '%'+@dateSuffix+'%' ORDER BY fName -- filter on the today's files only

   BULK INSERT [myDb].[mySchema].[myTable] 
      FROM 'C:\PRODUCTION\\' + @fileToImport + '' -- remove the extra slash from the path! This is so the StockOverflow color formatting works properly on the page.
         WITH (FIELDTERMINATOR ='|',ROWTERMINATOR = '\n')

   DELETE FROM @allFiles WHERE fName = @fileToImport -- remove processed file from the table variable

   SET @i = @i + 1
END

Note: I did not have time to test this and I think you may need to use a cursor instead of my WHILE loop but in theory this should give you some idea.

Bulk Insert file name Like SQL server

2 Answers2