Easy way to get list of rows that fail check constraints

• DataDog

  Hall of Fame

  Points: 3305

  More actions

  July 7, 2010 at 2:38 am

  #141158

  insert into dbo.Table ([field1], [field2], [field3])

  select [field1], [field2], [field3]

  from dbo.NewData

  some of the records in dbo.NewData conflict with CHECK constraints

  so the whole transaction rolls back

  what is the easiest way to get a list of the records which are conflicting?

  is there an equivalent of IGNORE_DUP_KEY but for CHECK constraints?

  or a version of OUTPUT that outputs the records that failed to insert?

  I can think of many ways to approach this - but I am wondering if there is a quick and easy trick?

  Especially when there are numerous CHECK constraints, millions of rows and only a few "stragglers"

• Mike01

  SSChampion

  Points: 11395

  More actions

  July 7, 2010 at 12:14 pm

  #1189790

  what are the constraints on the new table? You will probably end up with something like Field1 not in (1,2,3) and Field2 not in (4,5,6), etc...

  For better, quicker answers, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/