SQL Server insert statement

Question

In a database insert statement that could potentially insert more than one row, if there is an error in 1 row (of many), how will i know what the exact error is - which row and which column? Any ideas?

Answer 1

If an error occurs, the transaction will be rolled back, and no rows will be inserted.

Even if you don't explicitly start a transaction using begin transaction, SQL Server won't run an insert statements outside a transaction. By default, it will start one for you and commit it after each statement. See this MSDN article on auto commit.

To figure out the exact row, you could insert them one at a time, maybe using a temporary table:

select * into #tmp from <YourInsertQuery>
while @@rowcount > 0
    begin        
    insert into TargetTable 
    select top 1 * 
    from #tmp tmp
    where not exists (
        select * 
        from TargetTable tt
        where tt.id = tmp.id
    )
    order by id
    end

Once this fails, the top row in #tmp that's not in TargetTable is causing the error.

Answer 2

First of all it depends on what error you get. There are plenty of errors that can happen for which there is no particular row at fault. But let consider than the error is a constrain violation that is caused by a particular row. In that case the fact that rows are inserted together means they are a single transactional update, so they all have to succeed or all have to fail, usually there is no sense in inserting half the rows because it leave the database inconsistent.

Finaly lets consider that consistency is not an issue and you really want to insert only the rows that can succeed. In that case you should look at the error message because it will be indicative of what is the problem. If is a primary constrain violation, then the problem si the row with a duplicate key. A check constrain, then is the row that fails the check. The culprit can be revealed by inspection of the error message, inspection of the target table and inspection of the candidate rows to be inserted.

If you want to automate the process and continue after a constraint failure you have no choice but to insert rows one at a time. In that case the failure will happen only for one row, and that is the row with the problem.