Bulk insert with list of files

Question

I create a SQL procedure to import data from txt file. However there are lots of files(about 80 files) and i cannot list its name. Its name formatted like 'DATA_XXXXXXX.TXT'

DECLARE @sql1 nvarchar(max) = N'BULK INSERT dbo.Student FROM '''
  + 'D:\NEW_FOLDER\DATA_20190222'
  + '.TXT'
  + ''' WITH 
     (
       FIELDTERMINATOR = ''|'',
       MAXERRORS = 10000
     );';

EXEC sys.sp_executesql @sql1;

I want all data on theses file should be loaded into table. How can I do that?

Possible duplicate of [Import Multiple CSV Files to SQL Server from a Folder](https://stackoverflow.com/questions/16076309/import-multiple-csv-files-to-sql-server-from-a-folder) — Killer Queen, Apr 09 '19 at 11:38

Ian-Fogelman · Answer 1 · 2019-04-09T11:47:28.983

You could do it with a while loop, insert the values into a temptable and increment the file name variable with dynamic SQL:

CREATE TABLE #TEMP_FILENAMES
(
FILENAME VARCHAR(50)
)

INSERT INTO #TEMP_FILENAMES
VALUES('20190222')

INSERT INTO #TEMP_FILENAMES
VALUES('20190223')

DECLARE INT @YEARMMDD
WHILE EXISTS(SELECT * FROM #TEMP_FILENAMES)

BEGIN
SET @YEARMMDD = (SELECT TOP 1 FILENAME FROM #TEMP_FILENAMES)

DECLARE @sql1 nvarchar(max) = N'BULK INSERT dbo.Student FROM '''
  + 'D:\NEW_FOLDER\DATA_' + @YEARMMDD 
  + '.TXT'
  + ''' WITH 
     (
       FIELDTERMINATOR = ''|'',
       MAXERRORS = 10000
     );';

EXEC sys.sp_executesql @sql1;

DELETE FROM #TEMP_FILENAMES WHERE FILENAME = @YEARMMDD 

END

`WHILEXISTS` isn't valid T-SQL. I assume you mean `WHILE EXISTS` — Thom A, Apr 09 '19 at 11:46

Bulk insert with list of files

1 Answers1