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?
kgittingerAsked:
Who is Participating?
 
Som TripathiConnect With a Mentor Database 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


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

How do I add that to the following?
BULK INSERT #tmp_Harvard_Feed
FROM 'mypath\extr_claim.txt'
WITH
(
ROWTERMINATOR = '\n'
 )
0
 
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


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.