INSERT Question (best way to do this)

• texpic

  SSCertifiable

  Points: 5887

  More actions

  September 26, 2006 at 2:04 pm

  #90498

  TableOne:

  AcctNo

  A

  B

  C

  TableTwo:

  A

  C

  INSERT INTO TableOne

  (AcctNo)

  SELECT

  AcctNo

  FROM TableTwo

  --WHERE TableOne.AcctNo <> TableTwo.AcctNo  ???

• John Rowan

  SSC Guru

  Points: 56440

  More actions

  September 26, 2006 at 3:14 pm

  #662589

  You want to insert into TableTwo the values that exist in TableOne, but not yet in TableTwo?  If so, you need an OUTER JOIN like this:

  declare @TableOne table (AcctNo char(1))

  declare @TableTwo table (AcctNo char(1))

  INSERT INTO @TableOne

  SELECT 'A' UNION ALL

  SELECT 'B' UNION ALL

  SELECT 'C'

  INSERT INTO @TableTwo

  SELECT 'A' UNION ALL

  SELECT 'C'

  INSERT INTO @TableTwo

  SELECT One.AcctNo

  FROM @TableOne One

      LEFT JOIN @TableTwo Two

      ON One.AcctNo = Two.AcctNo

  WHERE Two.AcctNo IS NULL

  SELECT *

  FROM @TableTwo

   

  John Rowan

  ======================================================
  ======================================================
  Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden