question

Sanbagavalli avatar image
Sanbagavalli asked

Eliminate Last Row

Hi team, I need to eliminate the last row from a file and load the rest of the data into table through bulk insert in sql. Last rows contains the counts like (12000 rows affected) Is there any way which would make the last row get eliminated? Regards, Sanbagavalli
sql-server-2008
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

How are you inserting the SQL? I know you mentioned a bulk insert but using what method? SSIS? BCP? Just an OPENROWSET? FYI - OPENROWSET has a LASTROW parameter that might work nicely depending on your needs. You should determine the row count and then just subtract one.
2 Likes 2 ·

1 Answer

· Write an Answer
Tom Staab avatar image
Tom Staab answered
As John suggested, the LASTROW option (available in different ways via OPENROWSET, BCP, or BULK INSERT) can be used, but you'll need to know the total number of rows and then subtract 1. I can think of a couple of options available if you don't already know the row count in advance, but having to count the rows first is not the most efficient because it requires reading (or at least scanning) the table twice. This leads me to one of my least favorite (most disliked?) defaults in SQL Server. A lot of people don't realize this, but by default a bulk insert will only fail after 10 errors. Why not 0? In your case, I suggest setting it to 1. You can expect the last line to fail, so setting it to 1 means you'll be okay as long as that's the only bad line. Any rows with errors fewer than the limit are simply ignored. Here is how to use the option for each of the 3 bulk insert methods. BULK INSERT ... WITH (MAXERRORS = 1); SELECT * FROM OPENROWSET(BULK ..., MAXERRORS = 1); bcp -m 1 And, please, use this option (with 0 or another appropriate value) in all bulk inserts to override the default of 10 ... unless you're okay ignoring the first 10 errors.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.