SQL Puzzle | Can you Skip Header Row in BULK INSERT? | SQL INTERVIEW QUESTION

In this we shall understand Can we can skip the rows using BULK INSERT and what should be the criertia to skip the rows etc.

BULK INSERT command is used to Imports a data file into a database table or view in a user-specified format in SQL Server.

Syntax

--

BULK INSERT   
   [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ]   
      FROM 'data_file'   
     [ WITH   
    (   
  .... 
[ [ , ] FIRSTROW = first_row ] 

--

First lets go through what the MSDN says about this.

FIRSTROW = first_row
Specifies the number of the first row to load. The default is the first row in the specified data file. FIRSTROW is 1-based.

Note-
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.

Now lets go through some examples with their output.

Example 1 | Header is different from the rows terminator and we are using FIRSTROW = 1

--CSV DATA

AB
2 , Pawan


--SCRIPT to GET DATA IN THE TABLE

DELETE FROM TestNulls

BULK
INSERT TestNulls
FROM 'E:\V.csv'
WITH
(
	FIRSTROW = 1,
	FIELDTERMINATOR = ',',
	ROWTERMINATOR = '\n'
)
GO

SELECT * FROM TestNulls

--

OUTPUT

--

Msg 4864, Level 16, State 1, Line 33
Bulk load data conversion error 
(type mismatch or invalid character for the specified codepage) 
for row 1, column 1 (Id).


Id          Nm
----------- ------------------
2            Pawan

(1 row affected)

--

Example 2 | Header is SAME as the rows terminator and we are using FIRSTROW = 1

--CSV DATA

A,B
2 , Pawan


--SCRIPT to GET DATA IN THE TABLE

DELETE FROM TestNulls

BULK
INSERT TestNulls
FROM 'E:\V.csv'
WITH
(
	FIRSTROW = 1,
	FIELDTERMINATOR = ',',
	ROWTERMINATOR = '\n'
)
GO

SELECT * FROM TestNulls

--

OUTPUT

--

Msg 4864, Level 16, State 1, Line 54
Bulk load data conversion error 
(type mismatch or invalid character for the specified codepage) for row 1
, column 1 (Id).

Data NOT INSERTED.

Id          Nm
----------- -------------------

(0 rows affected)

--

Example 3 | Header is different from the rows terminator and we are using FIRSTROW = 2

--CSV DATA

AB
1 , 
2 , Pawan

--SCRIPT to GET DATA IN THE TABLE

DELETE FROM TestNulls

BULK
INSERT TestNulls
FROM 'E:\V.csv'
WITH
(
	FIRSTROW = 2,
	FIELDTERMINATOR = ',',
	ROWTERMINATOR = '\n'
)
GO

SELECT * FROM TestNulls

--

OUTPUT

--

Id          Nm
----------- ----------------
2            Pawan

(1 row affected)

--

Example 4 | Header is same as the rows terminator and we are using FIRSTROW = 2

--CSV DATA

A,B
1 , 
2 , Pawan

--SCRIPT to GET DATA IN THE TABLE

DELETE FROM TestNulls

BULK
INSERT TestNulls
FROM 'E:\V.csv'
WITH
(
	FIRSTROW = 2,
	FIELDTERMINATOR = ',',
	ROWTERMINATOR = '\n'
)
GO

SELECT * FROM TestNulls

--

OUTPUT

--

Id          Nm
----------- -------------------
1            
2            Pawan

(2 rows affected)

--

Final Thoughts

Although you can use FIRSTROW attribute to skip rows. When the SQL SERVER skips the rows it only checks the field terminators. It will not validate the data in the fields of skipped rows. So if your header is of same format(column terminator and row terminator) then header or any row can be skipped

Reference

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com