joining table to intself and comparing

• blnbmv

  SSC Eights!

  Points: 900

  More actions

  August 11, 2008 at 9:22 am

  #195815

  hi members,

  The following are the tables I use:

  declare @totable table (gid int,parent int,id int,protype int)

  insert into @totable values(4,29246,278397,2)

  insert into @totable values(4,277124,29246,2)

  insert into @totable values(4,278937,2788399,1)

  Create table fromtable (Tgid int,Tparent int,Tid int,Tprotype int)

  insert into fromtable values(4,278397,278399,1)

  insert into fromtable values(4,29246,278397,1)

  insert into fromtable values(4,277124,29246,1)

  insert into fromtable values(4,35930,277124,1)

  insert into fromtable values(4,4,35930,1)

  insert into fromtable values(4,4,4,1)

  I have a Temp table(fromtable) with fresh data coming from different table and Target table(Totable) which has some records existing and I need to match the records based on gid,parent,id with Tgid,Tparent,Tid of Target table and Target table should get inserted with those where there are no matching records and less than protection of the target table records.(ie)

  I dont want to disturb the Target table records which have Tprotype level 2 and i just want my target table to look like this

  4,278397,2788399,1

  4,29246,278397,2

  4,277124,29246,2

  4,35930,277124,1

  4,4,35930,1

  4,4,4,1

  I want select statement which insert into target table by select the temptable records and matching with target table and check the logic and then insert.

  Thanks in advance,

  Sainath

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  August 11, 2008 at 9:43 am

  #856184

  I think you have a typo in the test data because I think you wanted the data in the first insert of fromtable to match the last insert of @totable, but they don't so my test gave me 7 records instead of 6.

  [font="Courier New"]DECLARE @totable TABLE (gid INT,parent INT,id INT,protype INT)

  INSERT INTO @totable VALUES(4,29246,278397,2)

  INSERT INTO @totable VALUES(4,277124,29246,2)

  INSERT INTO @totable VALUES(4,278937,2788399,1)

  DECLARE @fromtable TABLE  (Tgid INT,Tparent INT,Tid INT,Tprotype INT)

  INSERT INTO @fromtable VALUES(4,278397,278399,1)

  INSERT INTO @fromtable VALUES(4,29246,278397,1)

  INSERT INTO @fromtable VALUES(4,277124,29246,1)

  INSERT INTO @fromtable VALUES(4,35930,277124,1)

  INSERT INTO @fromtable VALUES(4,4,35930,1)

  INSERT INTO @fromtable VALUES(4,4,4,1)

  SELECT * FROM @totable

  INSERT INTO @totable

     SELECT

         tgid,

         tparent,

         tid,

         tprotype

     FROM

         @fromtable F LEFT JOIN

         @totable T ON

             F.tgid = T.gid AND

             F.tparent = T.parent AND

             F.tid = T.id AND

             /* this eliminates the protype 2's from the compare so any

             @fromtable rows which match the rest of the criteria would

             be inserted. if you never want to insert protype = 2 rows you

             should put F.tprotype <> 2 in the where clause (commented out

             below */

             T.protype = 2

     WHERE

         -- F.tprotype <> 2 And

         T.gid IS NULL -- this means only new records

  SELECT * FROM @totable[/font]

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question