Merging two tables

• jspencer-506909

  Valued Member

  Points: 69

  More actions

  October 8, 2007 at 4:52 am

  #182509

  Hi sorry if im posting in the incorrect forum i am new here and have been looking around and this seemed to be the right place.

  I have 2 tables populated with data from 2 different suppliers.

  I would like to merge the tables but there are some duplicate products ( indexed by model name), but choose the duplicate with the lowest price.

  Is there an easy way of doing this in ssis ?

  Thank you

  John Spencer

• Matt Miller (4)

  SSC Guru

  Points: 124210

  More actions

  October 8, 2007 at 7:52 am

  #740003

  It's a reasonably straightforward thing to do in T-SQL, so can that be coded easily into a SSIS package? sure. You're not giving any specifics, but I can give you a running start.

  First - let's start with the assumptions. Since you're dealing with SSIS, I'm assuming you're starting off with 2 non-SQL lists and you want to end up with 1 SQL list. So - my process would be something like the following:

  1. create 2 temp tables for the two lists (no constraints on it at all) - call them #List1 and #list2

  Create table #list1 (modelname varchar(xxx), etc....)

  Create table #list2 (modelname varchar(xxx), etc....)

  2. import the data from the 2 external files.

  3. clean up the data/get the formats consistent.

  4. once it's clean - index BOTH tables on modelname

  5. Now - use #list1 as the "base table" and merge #list2 into #list1

  --MERGE is an UPDATE and an INSERT combined

  Update #list1

  set #list1.field1=#list2.field1,

  #list1.field2=#list2.field2 --do this for every column defined other than price and modelname

  from #list1 inner join #list2 on #list1.modelname=#list2.modelname

  where #list1.price<#list2.price

  INSERT #list1 ( )

  select from #list2 where #list2.modelname not in (select modelname from #list1)

  6. Voila - #list1 is your combined list - you can throw away #list2.

  ----------------------------------------------------------------------------------
  Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

• Jeff Moden

  SSC Guru

  Points: 1004648

  More actions

  October 8, 2007 at 8:56 pm

  #740216

  Heh... I cheat like hell... load up one staging table with everything... delete the dupes with the "earliest" information... flip the pass-through-view to look at the new table... drink beer 😉

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)