Update with table from diff database

  • Hi

    I want to update a table Emp(DB1) from table Emp(DB2). I want to append new rows to it and also update any changes in the existing rows.How can I do this when table Emp(DB1) has relationships with another tables with in the DB1. I was trying to drop Emp, recreate Emp and fill Emp with new data but cudnt do it since it has references.

    can any one give some idea how to accomplish this task.

  • To add new rows:

    INSERT db1.dbo.Emp (col1, col2) SELECT col1, col2 FROM db2.dbo.Emp

     

    To update existing rows:

    USE db1

    UPDATE a

    SET    a.col1 = b.col1

    FROM  db1.dbo.Emp a

    JOIN   db2.dbo.Emp b

    ON     a.primaryKey = b.primaryKey

    Also, there is more info to read on SELECT and UPDATE in BOL (Books On Line)

    /Kenneth

  • And in addition to what Kenneth said, if the second database "DB2" resides on a separate instance, you would need to use linked servers.  Look up linked servers in BOL for more info..

  • Say you have 2 tables in both databases:

    Create table thing (

      Id int IDENTITY(1,1),

      Name nvarchar(50) NOT NULL,

      typeId int NOT NULL)

    Create Table ThingType (

      Id int Identity (1,1),

      Name nvarchar (50) NOT NULL )

    INSERT INTO db1..Thing (Name, TypeId)

    SELECT T2.Name, TP1.Id

    FROM db2..Thing T2

    INNER JOIN db2..ThingType TP2 on T2.TypeId = TP2.Id

    INNER JOIN db1..ThingType TP1 on TP2.Name = TP1.Name

    WHERE NOT EXISTS (select 1 from db1..Thing T1

                                where T1.Name = T2.Name and T1.TypeId = TP1.Id)

    Use the same approach for UPDATE

    _____________
    Code for TallyGenerator

  • Actually, there's no need to check for existing rows on an update, since if rows do exist, they update, and if not, nothing happens. Updates doesn't risk causing pk violations like inserts do.

    /Kenneth

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply