1

I have a folder that is static with a daily txt file that goes into the folder. The file name is the date. If the file name has the same name every day, everything works. Is there a way I can have my script pull any txt file in the folder? (Note, file comes in, get's processed and then I have an automatic transfer that moves the file after it has been inserted to a processed folder). So there will only be one file at a time in the folder. I hope that makes sense.

Here is the script for the bulk insert:

Bulk Insert Mydata.dbo.cust_adj 
From 'C:\MyData\FlatFiles\UnprocessedAdjReport\importformat.txt' 
With 
(
FieldTerminator= '|', 
Rowterminator= '\n' 
) 
Go

(I've got this saved as a stored procedure btw)

So "importformat" is just the name I used while setting up my scripts, going forward it will be in bb-yyyy-mmdd-hhmmnnnn.txt, as soon as the file is inserted, I move the file from the unprocessed folder to the processed folder. There will only be the one file each day.

If anyone has any advice or assistance with this, I would greatly appreciate it.

Pondlife
  • 15,992
  • 6
  • 37
  • 51
SQL_Noob
  • 1,161
  • 1
  • 9
  • 18
  • You should add the relevent tags for the database system you are using. Otherwise the right people will probably not see your question. – Chris Farmiloe Apr 04 '13 at 21:07
  • Ok, I added SQL Server 2008 as a tag. It's actually in SQL Server 2008 R2 Express – SQL_Noob Apr 05 '13 at 12:24
  • You need to build the file name dynamically as described in [this question](http://stackoverflow.com/questions/5547393/bulk-insert-with-variable-file-name). But personally I would do it outside the database using a small script in PowerShell or some other convenient language: TSQL is really weak for working with the file system and parsing/generating text. – Pondlife Apr 05 '13 at 15:51