Merging Tables

• Ruprecht

  SSC Eights!

  Points: 968

  More actions

  June 16, 2005 at 10:02 am

  #164027

  Hey All,

  I am merging two tables from separate databases that may contain duplicate entries in two columns, account and account_num. If there is a duplication, I want to add 'OLD' at the end of each entry in the originating table. The destination entry should stay the same. I'm a bit of a TSQL newbie and I could use some help getting a step in the right direction.

  Thanks in advance!

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  June 16, 2005 at 10:10 am

  #566188

  Maybe you need something like this.

  Update Main set Col = 'old' from dbo.OldTable Main inner join

  (

  Select O.Oldid from OldDb.dbo.OldTable O inner join

  NewDB.dbo.NewTable N on O. Oldid = N.NewId) dtDuplicates

  on Main.Oldid = dtDuplicates.Oldid

• Roy Rogers

  SSCertifiable

  Points: 7712

  More actions

  June 16, 2005 at 10:13 am

  #566192

  Check the results before you insert

  INSERT INTO Database1.dbo.Account (Cols) Values

  SELECT * FROM

  (

  SELECT COALESCE(DB1.Account, DB2.Account + 'OLD') Account,

   DB2.account_num,

   DB2.RestOftheColumns

  FROM

   Database2.dbo.Account DB2

  LEFT OUTER JOIN

   Database1.dbo.Account DB1

  ON

   DB2.account = DB21.account AND

   DB2.account_num = DB21.account_num) Results

  Regards,
  gova