import - Importing a txt file into SQL Server with a where clause

Question

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.

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?

Answer 1

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

Hope this helps.