BULK INSERT first row

Question

BULK INSERT first row

Steve Jones - SSC Editor

SSC Guru

Points: 736254
More actions
April 6, 2015 at 8:04 pm

#100906

Comments posted to this topic are about the item BULK INSERT first row
April 7, 2015 at 6:31 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Interesting question, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 2

Thank you for the post, Steve, good one. (I use this utility all the time)

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

Rune Bivrin SSCrazy Eights Points: 8512 More actions · Answer 3

I was very torn between "3" and "Not supported". Based on the following in the cited documentation, I kind of felt both were correct:

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.

It isn't supported, but you can do it.

Just because you're right doesn't mean everybody else is wrong.

Carlo Romagnano SSC-Insane Points: 22670 More actions · Answer 4

Rune Bivrin (4/7/2015)
I was very torn between "3" and "Not supported". Based on the following in the cited documentation, I kind of felt both were correct:
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.
It isn't supported, but you can do it.

I tried but I think I'm confused.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

Rune Bivrin (4/7/2015)
I was very torn between "3" and "Not supported". Based on the following in the cited documentation, I kind of felt both were correct:
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.
It isn't supported, but you can do it.

Same here. You can skip rows, but not headers? What's the difference?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

tom.w.brannon Hall of Fame Points: 3976 More actions · Answer 6

tom.w.brannon

Hall of Fame

Points: 3976

April 7, 2015 at 5:12 am

#1789148

Agreed

Ed Wagner SSC Guru Points: 287026 More actions · Answer 7

Koen Verbeeck (4/7/2015)
Rune Bivrin (4/7/2015)
I was very torn between "3" and "Not supported". Based on the following in the cited documentation, I kind of felt both were correct:
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.
It isn't supported, but you can do it.
Same here. You can skip rows, but not headers? What's the difference?

There is no difference. They may say that FIRSTROW isn't meant for skipping rows, but it is.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 8

Also, BCP command utility works in a same manner with FIRSTROW. As the text file's data has no headers, so based on the row terminator, it instructs to read from certain position.

-F first_row

Specifies the number of the first row to export from a table or import from a data file. This parameter requires a value greater than (>) 0 but less than (<) or equal to (=) the total number rows. In the absence of this parameter, the default is the first row of the file.

first_row can be a positive integer with a value up to 2^63-1. -Ffirst_row is 1-based.

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

webrunner SSC-Dedicated Points: 31865 More actions · Answer 9

Thanks - good question.

- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Stephanie J Brown Hall of Fame Points: 3896 More actions · Answer 10

Stewart "Arturius" Campbell (4/7/2015)
Koen Verbeeck (4/7/2015)
Rune Bivrin (4/7/2015)
I was very torn between "3" and "Not supported". Based on the following in the cited documentation, I kind of felt both were correct:
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.
It isn't supported, but you can do it.
Same here. You can skip rows, but not headers? What's the difference?
AS far as BULK INSERT is concerned, all rows in a file are data rows.

Ah, but it depends - on how the rows are formatted. The key here seems to be the part about

...looks only at the field terminators...

See the question on StackOverflow at http://stackoverflow.com/questions/1029384/sql-bulk-insert-with-firstrow-parameter-skips-the-following-line for an example of the behavior.

Here there be dragons...,

Steph Brown

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 11

Rune Bivrin (4/7/2015)
I was very torn between "3" and "Not supported". Based on the following in the cited documentation, I kind of felt both were correct:
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.
It isn't supported, but you can do it.

Find this BOL entry somewhat strange, although there is no "Skip Headers" knob, the FIRST ROW serves the purpose when the row delimiters are the same for the Header and the Rows, think someone must have over-speculated on the BOL entry.

😎

Hany Helmy SSChampion Points: 13921 More actions · Answer 12

I don`t remember loading a flat file into a SQL table since long time, so it`s good to know a new stuff 🙂

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 13

Thanks for the question. It seemed logical that you could just tell it which line to start on but calling it a header and the doc saying you can't skip headers was a little confusing.