How to modify bulk insert to use a wildcard

How can I modify my bulk insert SQL to use a wildcard?  The first of the filename will always be the same, but appended with a date and time. Current file is: MyCompany_Name_1022011_195810.txt and I

BULK INSERT #tmp_Test_Feed
FROM ''W:\MyCompany_Name_1022011_195810.txt
WITH
(
ROWTERMINATOR = '\n'
 )
kat50Asked:
Who is Participating?
 
JestersGrindConnect With a Mentor Commented:
I hard coded the date and time as an example.  You have to have the name of the file for it to work.  It cannot be a wild card.  

Take a look at the article below.  It provides a way to get the name of the file and then import it using BULK INSERT.

http://www.databasejournal.com/features/mssql/article.php/3325701/Import-multiple-Files-to-SQL-Server-using-T-SQL.htm

Greg

0
 
JestersGrindCommented:
You'll have to use dynamic SQL.  Something like this.

DECLARE @DateTime NVARCHAR(25), @SQL NVARCHAR(MAX)

SET @DateTime = '1022011_195810'

SET @SQL = 'BULK INSERT #tmp_Test_Feed
FROM ''W:\MyCompany_Name_' + @DateTime + '.txt''
WITH
(
ROWTERMINATOR = ''\n''
 )'
 
 EXECUTE sp_executesql @SQL
 
 Greg

0
 
kat50Author Commented:
I would like to not have to declare the date and time of the file.  Can I replace the + @DateTime + with '%'?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.