Merge issue

• SGT_squeequal

  SSCertifiable

  Points: 7167

  More actions

  January 8, 2013 at 9:57 am

  #287306

  Ola everyone, cam someone help with a Merge Query, so here is what i want to achieve, i have a source and target table, the source table may contain records that do not exist in the target table however, i do not want to insert these records into the target table, i want to insert them into a separate table is this possible?

  DDL

  Source table

  create table #src (RN int,value varchar (10),location varchar(10))

  insert into #src select 1,'A123','C:\temp' union all

  select 2,'A123','C:\temp' union all

  select 3,'A123','C:\temp' union all

  select 1,'B123','C:\temp' union all

  select 2,'B123','C:\temp' union all

  select 3,'B123','C:\temp'

  destination table

  create table #tgt (RN int,value varchar (10),location varchar(10))

  insert into #tgt select 1,'A123',Null union all

  select 2,'A123',Null union all

  select 1,'B123',Null

  Merge query

  merge #results as target

  using

  (

  select RN,VALUE,LOCATION from #src) as source

  on (target.value=source.value and target.rn=source.rn)

  WHEN MATCHED THEN

  UPDATE SET target.location=source.location

  WHEN NOT MATCHED BY target THEN

  **** i want to add in something like this here ****

  insert into some_other_table 'stringvalue'+source.value+'does not have a free slot',getdate() ;

  there is a bit more processing to get to this point and this is the last stage of my script to for any items that are not matched in the target means there is no slot available and I can not just create one I have to log out the info to another table can any one help.

  Thanks

  ***The first step is always the hardest *******

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  January 8, 2013 at 10:02 am

  #1574918

  so you want to INSERT into "some_other_table" where "value" and "rn" don't EXIST in #tgt

  and UPDATE #tgt WHERE value and rn match to #src ?

  Is that right? 😉

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]