SQL BULK INSERT skip Lastrow

Question

I am using BULK INSERT in SQL 2005 to upload flat file into table. The file contains header and trailor. I want to skip 1st row and last row.
I am using below comments (FIRSTROW = 1,LASTROW = -2)

BULK INSERT IMP_K2OTDaaaa from 'F:\WLL\InputFiles\Calyon\Lora\TEST\JPNC\Backedupfiles\Rec\KONCLPTOJPN_20120628045303.bkp'
 with (FormatFile = 'F:\WLL\InputFiles\Calyon\Lora\TEST\JPNC\Format\IMP_K2OTDaaaa.fmt',FIRSTROW = 1,LASTROW = -2,TabLock)

However , I am getting the below error , it seems last row = -2 is not accepting.

Please help .

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.

Thanks for your help in advance

Answer 1

Hi Prakash, the LASTROW value refers to the row number where you want to stop, so a value of -2 would not work in this case.

Here is an example of using dynamic SQL to use a variable value based on the row count in the table:
http://www.sql-questions.com/microsoft/SQL-Server-Programming/33048867/bulk-insertlastrow-attribute.aspx

Thanks,
Sam Lester (MSFT)

---

This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.