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;