How to skip the invalid rows while inserting the data into Database

Question

We have a statement., that is inserting some rows in a temporary table (say e.g., 10 rows), while inserting 5th row, it has some issue with one of the column format and giving an error and then it stopped inserting the rows. What I want is, it should skip the error rows and insert valid rows. For those error rows, it can skip that error column and insert with some null value & different status.

create table #tb_pagecontent_value (pageid int,formid uniqueidentifier, id_field xml,fieldvalue xml,label_final xml)
…
…

insert into #tb_pagecontent_xml
      select A.pageid,B.formid,A.PageData.query('/CPageDataXML/control') 
      from Pagedata A inner join page B on A.PageId=B.PageId
      inner join FormAssociation C on B.FormId=C.FormId
      where B.pageid in (select pageId from jobs where jobtype='zba' and StatusFlag!=1)

in the above e.g., I want to apply that logic. Any help is appreciated.

Answer 1

Relational databases do not work row-by-row but instead by sets. Thus, each insert statement is an encapsulated operation: either the entire operation works or the entire insert is rolled back. That said, some database systems, notably MS Access, will allow for overlooking rows that fail validation. However, behind the scenes, Access is inserting each row one at a time instead of as a set. Most DBMS interfaces will not do this including SQL Server. If you want to skip rows that fail validation, you need to filter them out of the insert.