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