Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Query - Join on a table in another database? Expand / Collapse
Author
Message
Posted Monday, December 8, 2008 8:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 3:28 PM
Points: 148, Visits: 287
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
Post #615578
Posted Monday, December 8, 2008 8:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 3:28 PM
Points: 148, Visits: 287
Sorry, I got it to work with a small change to the method I just said didnt work!


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 :)
Post #615586
Posted Monday, December 8, 2008 8:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 3:28 PM
Points: 148, Visits: 287
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
Post #615587
Posted Monday, December 8, 2008 9:11 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 12:34 PM
Points: 31,181, Visits: 15,626
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #615608
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse