SQL Query - Join on a table in another database?

  • Hey all,

    I have been working in a test environment and the following statement works fine (both tables are on the same server).

    What I now need to do, to roll this part of the job live is to have a similar query for tables in 2 different databases. (i.e. employeemasterholdingarea is on server1, cutovertransfer is on server2 in live)

    Update a

    Set Transferflag=1

    From

    EmployeeMasterHoldingArea a

    Inner Join

    CutoverTransfer b On a.EmployeeMasterPayrollNumber=b.Employee_Id

    A quick overview of what I am trying to do;

    When an employee id is found to be present in one table (Cutovertransfer), a bit marker will be updated in the other table (employeemasterholdingarea) to say so.

    All tables are identical in live, with the only exception that the live table is on a different database and neither can be moved.

    I have tried to do the [ServerName].[DatabaseName].dbo.[TableName].[ColumnName] type query which doesn't work.

    I have already set up the other server\database as a linked server (entitled "ospsq1cutover") to run some distributed queries in another part of the job, if this can be used?

    Not sure how to perform a join on an openquery, if this is even the right way to go about it.

    Any help would be greatly appreciated.

    thanks

  • Sorry, I got it to work with a small change to the method I just said didnt work! :blush:

    If anyone's interested;

    Update a

    Set Transferflag=1

    From

    EmployeeMasterHoldingArea a

    Inner Join

    [osp-sq1].cutover.dbo.cutovertransfer b On a.EmployeeMasterPayrollNumber=b.employee_ID

    *(With osp-sq1 being "server2")

    Thanks and sorry if I wasted anyones time 🙂

  • PS, Still not sure if this is the best method to use.

    It does work, but if anyone has another method which may be more efficient, then please post a reply.

    thanks again

  • I'd set an view up on the linked server, select a, b, c from newserver.newdb.newtable, and then use the view for updates. That way you won't have to change much code as the table moves to a new server, or even comes back to another database on this server.

Viewing 4 posts - 1 through 3 (of 3 total)

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