Check for key violation before insert

• Tom Currier

  Old Hand

  Points: 375

  More actions

  June 9, 2006 at 11:48 am

  #172753

  I know this is a really common issue, but the solution escapes me

  /*** What I'm trying to do here is check for the existance of a record {e.g., key violation}

  while inserting records from a table that contains many duplicates.

  I've also tried using a NOT EXISTS in the WHERE clause without the

   LEFT OUTER JOIN, e.g., SELECT ... FROM #TEMP_WEB_NOTES

  Temp tables being used just to get this to work

  #web_note_1 contains dups

  #TEMP_WEB_NOTES Primary key is

  CC025_ORG_CODE

  CC025_EXT_ACCT_CODE

  CC025_NOTE_CLASS

  CC025_PROD_CODE

  ***/

  INSERT #TEMP_WEB_NOTES

  SELECT DISTINCT

   WN1.CC025_ORG_CODE,

   WN1.CC025_EXT_ACCT_CODE,

   WN1.CC025_NOTE_CLASS,

   WN1.CC025_PROD_CODE,

   WN1.CC025_NOTE_TEXT,

   WN1.CC025_HTML_TEXT

  FROM #WEB_NOTE_1 WN1

   LEFT OUTER JOIN #TEMP_WEB_NOTES T

   ON T.CC025_ORG_CODE = WN1.CC025_ORG_CODE

   AND T.CC025_EXT_ACCT_CODE = WN1.CC025_ORG_CODE

   AND T.CC025_NOTE_CLASS = WN1.CC025_NOTE_CLASS

   AND T.CC025_PROD_CODE = WN1.CC025_PROD_CODE

  WHERE ISNULL(T.CC025_ORG_CODE,'') = ''

   AND ISNULL(T.CC025_EXT_ACCT_CODE,'') = ''

   AND ISNULL(T.CC025_NOTE_CLASS,'') = ''

   AND ISNULL(T.CC025_PROD_CODE,'') = ''

  Tom

   

• CK Bhatia

  Say Hey Kid

  Points: 679

  More actions

  June 9, 2006 at 1:35 pm

  #642903

  Assuming that the 4 columns in your joins are the PK of the target table, you can try this

  INSERT #TEMP_WEB_NOTES

  SELECT  WN1.CC025_ORG_CODE,

   WN1.CC025_EXT_ACCT_CODE,

   WN1.CC025_NOTE_CLASS,

   WN1.CC025_PROD_CODE,

   WN1.CC025_NOTE_TEXT,

   WN1.CC025_HTML_TEXT

  FROM #WEB_NOTE_1 WN1

  where not exists (select 1 from #TEMP_WEB_NOTES T

     WHERE T.CC025_ORG_CODE = WN1.CC025_ORG_CODE

     AND T.CC025_EXT_ACCT_CODE = WN1.CC025_ORG_CODE

     AND T.CC025_NOTE_CLASS = WN1.CC025_NOTE_CLASS

     AND T.CC025_PROD_CODE = WN1.CC025_PROD_CODE

    &nbsp

  ---

  I feel the need - the need for speed

  CK Bhatia