So here’s a quick out of the box solution for . . . you guessed it, dynamically pulling all text files from a folder into a SQL Server table. On it’s own this script isn’t very powerful but it can be paired with SQL queries to make some powerful functionality.
To use simply change the @path variable to the directory path you’re targeting and as it states in the comments . . .
-- NOTE: MAKE SURE THE BACKSLASH IS INCLUDED AT THE END OF THE STRING -- *.txt IS REFERENCED IN THIS EXAMPLE BUT *.xls COULD ALSO BE USED