Someone at worked asked me yesterday how to capture the row number of a failed insert. At first, I suggested he should wrap a try … catch around his insert statement and if that doesn’t help, try sql profiler and looking at the errors event. Either of this worked because his insert statement was followed by a select statement, which will make the whole thing into one single batch. His SQL statement looked as followed and this table he is selecting from has 26 million rows.
insert into newTable
select * from oldTable
Thru some attempt, we found that the insert failed between row 685 and 700, but then what happens if later down the road, he runs into another failure. Manually checking it would be a pain in the ass. Fine, so he was going to cursor thru it but cursoring thru 26 million rows is also a bitch.
The easiest solution was SQL 2005 new function: ROW_NUMBER. The example from BOL did the trick, we just used the bolded section
USE AdventureWorks;
GO
WITH OrderedOrders AS
(
SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (ORDER BY OrderDate) AS ‘RowNumber’
FROM Sales.SalesOrderHeader )
SELECT *
FROM OrderedOrders
WHERE RowNumber BETWEEN 50 AND 60;
All we did was change the OrderDate column to a column in our table that had the primary key and the row that was failed was id.