Update records across two databases

• Unnikrishnan.C

  Old Hand

  Points: 320

  More actions

  April 15, 2005 at 3:32 am

  #76282

  Hi,

  I have to update records in a database based on the records in another database.

  The data types of the columns in these two tables are different.

  For e.g.

  I have a table (Table1) in database DB1, Scope is:

  DB1.user1.Table1

  CREATE TABLE Table1 (Site nvarchar(64))

  INSERT INTO Table1 (Site) VALUES ('1')

  INSERT INTO Table1 (Site) VALUES ('2')

  INSERT INTO Table1 (Site) VALUES ('3')

  INSERT INTO Table1 (Site) VALUES ('Site1')

  INSERT INTO Table1 (Site) VALUES ('Site2')

  In 2nd database, DB2 the table is Table2, Scope is:

  DB2.user2.Table2

  CREATE TABLE Table2 (OrgId INT, OrgName nvarchar(64))

  INSERT INTO Table2 (OrgId,OrgName) VALUES (1,'Site1')

  INSERT INTO Table2 (OrgId,OrgName) VALUES (2,'Site2')

  My requirement is:

  In DB1.user1.Table1, UPDATE all the entries SET Site = DB2.user2.Table2.OrgName

  WHERE DB1.user1.Table1.Site is a number

  AND DB1.user1.Table1.Site = DB2.user2.Table2.OrgID

  i.e. In DB1.user1.Table1, entries with Site='3' , 'Site1' and 'Site2' should not be disturbed.

  Thanks in advance

  Unnic

• Diederik Tuypens

  SSChasing Mays

  Points: 623

  More actions

  April 15, 2005 at 5:04 am

  #552883

  use db1

  go

  update user1.Table1

  set user1.Table1.Site = db2.user2.Table2.OrgName

  from user1.Table1

  inner join db2.user2.Table2 on user1.Table1.site = CONVERT(VARCHAR(10), db2.user2.Table2.OrgId)