Import text file into SQL Database AND include the file name

[bmacbmac]

Hi,

I am using the following query to import a text file into my DB:

bulk insert dbo.#temp
from 'E:\temp\Volume1.txt'
with (FIELDTERMINATOR = ',')

Is there a way I can also include the name of the text file? Something like:

bulk insert dbo.#temp
from 'Volume1.txt', 'E:\temp\Volume1.txt'
with (FIELDTERMINATOR = ',')

Thanks!

 

[simian336]

Not sure I understand the question... 'E:\temp\Volume1.txt' is the name of the file.

Simi

 

[bmacbmac]

Yes. The text file name is Volume1.txt. I'd like to insert that file name into my database on each row of data that came from that text file.

 

[simian336]

ah... depends on your data in the table. If every row has something in the filename field then once you run the bulk insert you could just

Update table set filename='E:\temp\Volume1.txt' where filename is null

Otherwise perhaps you have a date field when it was inserted or something similar. Otherwise you could add it to your text file.

Simi

 

[bmacbmac]

ok thanks.. i might have to do that. I have about 1000 text files to import so maybe I can import one, update filename and then repeat.

 

[fredericofonseca]

if you have 1k files, all with same layout (or at least within few groups of layouts) best thing would be to use SSIS to load them.

with SSIS you can setup a foreach container which will loop through one (or more folders), retrieve each file name, and then for each one you execute a dataflow task to load each file onto sql server. As part of this load you can add the filename as another field on the flow.

fast and easy to implement

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886