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)
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.