How to add a wildcard to a bulk insert command?

How to add a wildcard to a bulk insert command? I am useing Bulkinsert to brgin in data.  Data is always in the same directry with the same prefix (extr_claim), the suffix changes by adding a date (such as_03292011) to the end, then the .txt extension.  There will always be just 1 fine in the directry with the prefix.  How and I add something like FROM ' mypath \extr_claim*.txt' to my script?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SELECT * FROM filenames WHERE fn_name LIKE 'mypath\extr_claim%.txt'
kgittingerAuthor Commented:

How do I add that to the following?
BULK INSERT #tmp_Harvard_Feed
FROM 'mypath\extr_claim.txt'
Som TripathiDatabase AdministratorCommented:
You can try below method to get the file name -

This is will do xp_cmdshell to get the path of file and then it will load with the same file.

declare @tmp table ( value varchar(128)) 
declare @file_path varchar(128), @file_name varchar(128)
declare @cmd varchar(2000)

--Place the file-path here--
set @file_path='E:\Dimension\Tests\' 
set @cmd='dir /b ' + @file_path + 'extr_claim*'

insert into @tmp 
exec master..xp_cmdshell @cmd

select @file_name= @file_path + value from @tmp where isnull ( value , '<>') <> '<>' 

set @cmd = 'BULK INSERT #tmp_Harvard_Feed
FROM '''+ @file_name  + 
''' WITH 
(   ROWTERMINATOR = ''\n'' )  
exec (@cmd)

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Som TripathiDatabase AdministratorCommented:
If xp_cmdshell is disabled, you can enable it using -

exec sp_configure 'advanced' , 1 
reconfigure with override

exec sp_configure 'xp_cmdshell' , 1 
reconfigure with override

exec sp_configure 'advanced' , 0 
reconfigure with override

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.