Bulk Insert Multiple pipe delimted files

Transact-SQL
1

Hello everyone,

I am able to import one pipe delimited file at a time with the script below but can't get it to import multiple files at once. I tried to use a wildcard in the file name but it won't allow it (ie. .txt or eJ.text).

Any assistance that you can provide would be greatly appreciated.

Thank you,

BULK INSERT databasename.tablename
FROM 'C:\temp\EJ_Detail_file.txt'
WITH
(
FIELDTERMINATOR ='|',
FIRSTROW = 2,
ROWTERMINATOR ='\n'
)

2

BULK INSERT does only process one file at a time. It won't do multiple files at once and won't accept a wildcard in the file name.

3

Try:

DECLARE
     @DirTree table (subdirectory varchar(255), depth tinyint, is_file bit);
DECLARE 
     @path varchar(255)='C:\temp'
   , @bs varchar(1)='\'
   , @fullpath varchar(255)
   , @sql nvarchar(MAX);

INSERT @DirTree
   ( subdirectory
   , depth
   , is_file )
EXEC sys.xp_dirtree @path, 1, 1 ;

DECLARE FILES CURSOR FOR       
SELECT @path+@bs+dt.subdirectory 
FROM @DirTree dt;

OPEN FILES

FETCH FILES INTO @fullpath;

WHILE @@fetch_Status = 0
BEGIN
   SET @sql =
   'BULK INSERT databasename.tablename
   FROM '+@fullpath+'
   WITH
   (
   FIELDTERMINATOR =''|'',
   FIRSTROW = 2,
   ROWTERMINATOR =''\n''
   )'
   EXEC(@sql);
   FETCH FILES INTO @fullpath;
    
END
CLOSE FILES;
DEALLOCATE FILES;
4

Btw, SSIS does have built-in file looping and you can use wildcards with it.