Updating table from one database to another

• David Martin-281752

  Newbie

  Points: 2

  More actions

  November 30, 2005 at 5:27 am

  #169634

  Hi all

  I have just joined and I am not particularly great on writing sql but I need to find out if there is a way to update data in fields of one table in 1 database from another database on the same server.

  The update I can do if the same database but having problems what to do as the data is coming from another database.

  Using SQL 2000.

  I have a joining field that I can use in each database/table.

  Any help appreciated.

  Thanks

  Dave

• David Martin-281752

  Newbie

  Points: 2

  More actions

  November 30, 2005 at 6:59 am

  #606710

  Hi I have worked out what I need from a previous post, I think it is working how I want.

  UPDATE    Subset

  SET              Subset.Leadengineer = Contact2.ULEADENGIN

  FROM         heat2.Subset Subset INNER JOIN

                        Goldmine.dbo.Contact2 Contact2 ON Contact2.Accountno = Subset.SupportInfo

   

  Cheers

• Phil Parkin

  SSC Guru

  Points: 247183

  More actions

  November 30, 2005 at 8:54 am

  #606774

  Hi David - looks like you've cracked it.

  I would suggest adding a WHERE clause too:

  UPDATE    Subset

  SET              Subset.Leadengineer = Contact2.ULEADENGIN

  FROM         heat2.Subset Subset INNER JOIN

                        Goldmine.dbo.Contact2 Contact2 ON Contact2.Accountno = Subset.SupportInfo

  WHERE Subset.Leadengineer <> Contact2.ULEADENGIN

  to avoid any unnecessary disk writes.  Also, in your SET clause, you do not need to qualify the Leadengineer field - you've already told SQL Server that you're updating Subset in the UPDATE clause.  So the final version becomes:

  UPDATE    Subset

  SET          Leadengineer = Contact2.ULEADENGIN

  FROM        heat2.Subset Subset INNER JOIN

                        Goldmine.dbo.Contact2 Contact2 ON Contact2.Accountno = Subset.SupportInfo

  WHERE Subset.Leadengineer <> Contact2.ULEADENGIN

  ---