如何使用 T-SQL 读取文本文件? [英] How to read a Text file using T-SQL?

问题描述

使用 T-SQL 读取文本文件的最佳方法是什么?我见过 BULK INSERT 和许多不同的功能，但没有一个是我正在寻找的.

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.

除非我遗漏了某些内容，否则 BULK INSERT 不允许我添加额外的字段.

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.

推荐答案

您可能可以对临时表进行批量插入，然后对要添加的数据进行另一次插入连接.这是一个例子

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 =' \n')

/*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

这不是您想要的吗?

这篇关于如何使用 T-SQL 读取文本文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！