SQL Query - Join on a table in another database?

Question

Post reply

SQL Query - Join on a table in another database?

McSQL

SSCarpal Tunnel

Points: 4936
More actions
December 8, 2008 at 8:42 am

#71111

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

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

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

McSQL SSCarpal Tunnel Points: 4936 More actions · Answer 1

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 🙂

McSQL SSCarpal Tunnel Points: 4936 More actions · Answer 2

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

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 3

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.