Hi all,
Does anyone know if it's possible to pin-point what row failed, during the set operation in SQL Server 2000?

For example, I would like to insert 10 records with unique IDs ranging 1-10. Now, during the set operation, the insert fails on record whose ID is 6. I would like to isolate this record (get its information) and continue with the insert.
Is this possible or am I going to have to use LOOPs (or CURSORs)?

Also, I have to know which record failed.

Thanks in advance
Goran Djuranovic

In the example below, StagingTable contains the new rows to be inserted into ProductionTable. Those rows from StagingTable that contain IDs already in use will be inserted into RejectTable. By joining Staging with Production, we can avoid the use of a cursor or looping.

-- Insert rows into ProductionTable where the ID is not already in use.

insert into ProductionTable
select
    *
from StagingTable as S
    left join ProductionTable as P
        on P.ID = S.ID
where
    P.ID is null

-- Insert rows into RejectTable where the ID is already in use.

insert into RejectTable
select
    *
from StagingTable as S
    left join ProductionTable as P
        on P.ID = S.ID
where
    P.ID is not null


  Hi all,
  Does anyone know if it's possible to pin-point what row failed, during the set operation in SQL Server 2000?

  For example, I would like to insert 10 records with unique IDs ranging 1-10. Now, during the set operation, the insert fails on record whose ID is 6. I would like to isolate this record (get its information) and continue with the insert.
  Is this possible or am I going to have to use LOOPs (or CURSORs)?

  Also, I have to know which record failed.

  Thanks in advance
  Goran Djuranovic

Hi JT,
Thanks for your response, but you didn't answer my question. I cannot join on IDs, because IDs from StaginTable (table variable in my case) are auto-incremented when populated from an XML file, so they don't necesserally match the IDs from ProductionTable.

Thanks
Goran

  In the example below, StagingTable contains the new rows to be inserted into ProductionTable. Those rows from StagingTable that contain IDs already in use will be inserted into RejectTable. By joining Staging with Production, we can avoid the use of a cursor or looping.

  -- Insert rows into ProductionTable where the ID is not already in use.

  insert into ProductionTable
  select
      *
  from StagingTable as S
      left join ProductionTable as P
          on P.ID = S.ID
  where
      P.ID is null

  -- Insert rows into RejectTable where the ID is already in use.

  insert into RejectTable
  select
      *
  from StagingTable as S
      left join ProductionTable as P
          on P.ID = S.ID
  where
      P.ID is not null


    Hi all,
    Does anyone know if it's possible to pin-point what row failed, during the set operation in SQL Server 2000?

    For example, I would like to insert 10 records with unique IDs ranging 1-10. Now, during the set operation, the insert fails on record whose ID is 6. I would like to isolate this record (get its information) and continue with the insert.
    Is this possible or am I going to have to use LOOPs (or CURSORs)?

    Also, I have to know which record failed.

    Thanks in advance
    Goran Djuranovic

From your question, it sounded as if you have a situation where you are inserting from a staging table into a production table but the problem is that specific rows cause the insert to fail. I was thinking perhaps the situation is that the new row contains an ID that conflicts with the unique key constraint in the production table.
  Hi JT,
  Thanks for your response, but you didn't answer my question. I cannot join on IDs, because IDs from StaginTable (table variable in my case) are auto-incremented when populated from an XML file, so they don't necesserally match the IDs from ProductionTable.

  Thanks
  Goran

    In the example below, StagingTable contains the new rows to be inserted into ProductionTable. Those rows from StagingTable that contain IDs already in use will be inserted into RejectTable. By joining Staging with Production, we can avoid the use of a cursor or looping.

    -- Insert rows into ProductionTable where the ID is not already in use.

    insert into ProductionTable
    select
        *
    from StagingTable as S
        left join ProductionTable as P
            on P.ID = S.ID
    where
        P.ID is null

    -- Insert rows into RejectTable where the ID is already in use.

    insert into RejectTable
    select
        *
    from StagingTable as S
        left join ProductionTable as P
            on P.ID = S.ID
    where
        P.ID is not null


      Hi all,
      Does anyone know if it's possible to pin-point what row failed, during the set operation in SQL Server 2000?

      For example, I would like to insert 10 records with unique IDs ranging 1-10. Now, during the set operation, the insert fails on record whose ID is 6. I would like to isolate this record (get its information) and continue with the insert.
      Is this possible or am I going to have to use LOOPs (or CURSORs)?

      Also, I have to know which record failed.

      Thanks in advance
      Goran Djuranovic

Goran Djuranovic wrote:
That's because you didn't supply enough information. You didn't tell us
what "row failed" means. I assume you got an error message, so it would
help to state what the message is. Posting DDL also usually helps.

The point of JT's example is that if the error was a constraint
violation then you can use a query to filter out that row during the
INSERT. You can use a similar query to see which row(s) would have
failed. Since you didn't tell us what the error message is or show us
any data we can't give you the whole solution.

The short answer is, you have to use a cursor if you want isolate the exact row where the error occured.

JT supplied you with a process for eliminating the errors, in this case dupicate key errors.  You can also get null errors, data type conversion errors, and constraint errors, not to mention truncation of data which can be lost without generating an error.

Unless you know exactly what data is coming through and can identify the expected errors ahead of time, you will have to use a cursor or have your application loop therough the records and make individual calls to the database, which amounts to the same thing (although doing it in the app is more portable).  There is another alternative, and that is to use a DTS package to load the data and set the max errors accordingly.  When the package is done inserting into the table you can use a select similar to what JT provided to identify the failed rows.

  Hi all,
  Does anyone know if it's possible to pin-point what row failed, during the set operation in SQL Server 2000?

  For example, I would like to insert 10 records with unique IDs ranging 1-10. Now, during the set operation, the insert fails on record whose ID is 6. I would like to isolate this record (get its information) and continue with the insert.
  Is this possible or am I going to have to use LOOPs (or CURSORs)?

  Also, I have to know which record failed.

  Thanks in advance
  Goran Djuranovic

Have you looked at Savepoints.  They are a mechanism to roll back
portions of transactions. You create a savepoint using the SAVE
TRANSACTION savepoint_name statement, and then later execute a ROLLBACK
TRANSACTION savepoint_name statement to roll back to the savepoint
instead of rolling back to the start of the whole transaction.

Thanks to your responses, guys. I decided to go with the SET operation, and
before doing SET insert I just validate the data (not 100% safe but what is?
Goran Djuranovic

Show quoteHide quote

Create table as I need it, how to reference in query?
Help with Stored Procedure, Taking too long to run...
Determining single-column candidate keys
Formatting Data
send and return value from storedprocedure