Bulk Insert four different files into Four tables 1

[jeffwest21]

I have 4 files that are dumped automatically off a sftp site, three of the files have specific file names that do not change, however the 4th one does change as it is prefixed with the date of creation i.e. FileName-exp-2013-09-24.csv.

My bulk insert statement reads thus

BULK INSERT CDR FROM '\\DirPath\FileName-exp-2013-09-24.csv'
WITH (

DATAFILETYPE = 'char',
   FIELDTERMINATOR = ',',
   ROWTERMINATOR = '\n'
)

How do I add a wild card to the end of the file path between the exp-Date and .csv so that it reads this into the right table.

All the files are CSV format which is why I cannot just do *.csv, the files are autogenerated everyday and need to be dated stamped for later chacking if required so I cannot get the file named differently.

'Clever boy...'

 

[gmmastros]

You could use dynamic sql. Something like this...

Declare @SQL VarChar(8000)

Set @SQL = 'BULK INSERT CDR FROM ''~FileName~''
WITH (

DATAFILETYPE = ''char'',
   FIELDTERMINATOR = '','',
   ROWTERMINATOR = ''\n''
) '

Set @SQL = Replace(@SQL, '~FileName~', PutYourFileNameHere)

exec (@SQL)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jeffwest21]

Cheers will give this a go

'Clever boy...'