使用 where 子句将 txt 文件导入 SQL Server

2022-01-03 00:00:00 import where-clause sql-server sql-server-2012 flat-file

我有一个 6.00 GB 的 .txt 文件.它是一个制表符分隔的文件，所以当我尝试将它加载到 SQL Server 时，列分隔符是制表符.

I have a .txt file which is 6.00 GB. It is a tab-delimited file so when I try to load it into SQL Server, the column delimiter is tab.

我需要将该 .txt 文件加载到数据库中，但我不需要 6.00 Gb 文件中的所有行.我需要能够使用像

I need to load that .txt file into the database, but I don't need all the rows from the 6.00 Gb file. I need to be able to use a condition like 

select * 
into <my table> 
where column5 in ('ab, 'cd')

但这是一个文本文件，无法在该条件下将其加载到数据库中.

but this is a text file and am not able to load it into db with that condition.

谁能帮我解决这个问题?

Can anyone help me with this?

推荐答案

您是否尝试过 BULK INSERT 命令?看看这个解决方案:

Have you tried with BULK INSERT command? Take a look at this solution:

--Create temporary table
CREATE TABLE #BulkTemporary
(
  Id int,
  Value varchar(10)
)

--BULK INSERT has no WHERE clause
BULK INSERT #BulkTemporary FROM 'D:TempFile.txt'
WITH (FIELDTERMINATOR = '	', ROWTERMINATOR = '
')

--Filter results
SELECT * INTO MyTable FROM #BulkTemporary WHERE Value IN ('Row2', 'Row3')

--Drop temporary table
DROP TABLE #BulkTemporary

希望这会有所帮助.

相关文章