tsql - How to read a Text file using T-SQL?

Question

What's the best way to read a text file using T-SQL? I've seen the BULK INSERT and many different functions but non of them are what I'm looking for.

I need to read each line in the text file and then insert it into a table with some other information like filename, filelocation, status, record date & time created, etc.

The BULK INSERT does not allow me to add extra field unless I'm missing something on this.

Any help or pointing the right direction will be really appreciate it.

Answer 1

You could probably do bulk insert into a temp table and then do another insert joining with the data you want to add. Here is an example

CREATE TABLE #TEXTFILE_1(
    FIELD1 varchar(100) ,
    FIELD2 varchar(100) ,
    FIELD3 varchar(100) ,
    FIELD4 varchar(100));

BULK INSERT #TEXTFILE_1 FROM 'C:STUFF.TXT'
WITH (FIELDTERMINATOR =' | ',ROWTERMINATOR =' 
')

/*You now have your bulk data*/

insert into yourtable (field1, field2, field3, field4, field5, field6)
select txt.FIELD1, txt.FIELD2, txt.FIELD3, txt.FIELD4, 'something else1', 'something else2' 
from #TEXTFILE_1 txt

drop table #TEXTFILE_1

Does this not do what you'd like?