SQL Server BULK INSERT: why "The FIRSTROW attribute is not intended to skip column headers"?

2
votes

Here https://msdn.microsoft.com/en-us/library/ms188365.aspx I can read that:

The FIRSTROW attribute is not intended to skip column headers. Skipping headers is not supported by the BULK INSERT statement. When skipping rows, the SQL Server Database Engine looks only at the field terminators, and does not validate the data in the fields of skipped rows.

But I would like to do exactly this. Why is this not intended? Can I expect any problems when I do skip headers with FIRSTROW = 2?

2
sql-serverbulkinsert

2 Answers

3
votes

the SQL Server Database Engine looks only at the field terminators

That's your answer. If your column headers have names that include field terminators, the system will find the wrong number of fields in the first line, and great hilarity will thus ensue. You can imagine that in all the world's files, some column had 

Years, since 1970

as a header. Clear enough to the human, but the machine has only rules.

In case you're not aware, your bulk insert will fail in the general case for CSV files. CSV format is quite variable, and more complex than what bulk insert can interpret correctly. Specifically, there are quoting rules that allow commas to be included among the data. SQL Server won't honor the quotes. It will interpret the comma as a separator regardless. Like the man says,

the SQL Server Database Engine looks only at the field terminators

2
votes

It will work fine, but it´s need the first row be same format (mean that header must have same FIELDTERMINATOR and ROWTERMINATOR) and when use firstrow = 2 then that data will be inserted from the second row. Example if I have CSV file what to import and the first line is column header my code will be like this 

BULK INSERT myTable
FROM 'C:\myTable.csv'
WITH
(
   FIRSTROW = 2,
   FIELDTERMINATOR = ',',
   ROWTERMINATOR = '\n'
)
GO