MS SQL Bulk Insert

Question

I have a requirement to insert a large 2 GB CSV file into my MS SQL database. most of the rows in this is not required to insert. I did not find anything to filter rows while doing bulk insert.I am using MS SQL bulk insert command to do this. Is there any option to filter rows on mySQL/MSSQL/Oracle on bulk insert?

BULK INSERT payroll.t_allowance
   FROM 'f:\orders\lineitem.csv'
   WITH 
      (
         FIELDTERMINATOR =' |',
         ROWTERMINATOR =' |\n'
      );

Answer 1

You can use OPENROWSET with BULK option:

SELECT *
FROM OPENROWSET(BULK 'f:\orders\lineitem.csv', 
                FORMATFILE= 'f:\orders\format.xml') AS a
WHERE ... 

format.xml is a file where you configure delimeters, column names, terminators etc: https://msdn.microsoft.com/en-us/library/ms178129.aspx

Answer 2

To avoid the complexities of the format file, and with some other trade-offs, you can create a staging table, BULK INSERT the staging table, and use INSERT...SELECT to load the target table from the staging table.

-- Create a temporary staging table with the same column names and data types, but no indexes
-- Alternatively, use CREATE TABLE
-- When using a permanent table, use TRUNCATE TABLE
SELECT *
INTO #stage
FROM payroll.t_allowance
WHERE 1 = 0;

-- Bulk load the staging table
-- Use the TABLOCK hint to achieve minimally logged inserts
BULK INSERT #stage
FROM 'f:\orders\lineitem.csv'
WITH (TABLOCK, FIELDTERMINATOR = ' |', ROWTERMINATOR = ' |\n');

-- Load the target table from the staging table
INSERT INTO payroll.t_allowance
SELECT s.*
FROM #stage AS s
WHERE...;

-- Drop the staging table
-- or use TRUNCATE TABLE for a permanent table
DROP TABLE #stage;