Comparing 2 fields from 2 different databases and them importing data from one field in on DB to another field in another db based on Employee ID

[Ben]

I have 2 SQL Databases

One is called DeptSearchFields
The other is called AsureIDc51

Within these 2 SQL databases there are tables (like you didn't know
that already LOL)
The tables I'm concerned with are:

AsureIDc51 - A_870211EE_7122_4AB3_B513_96B2AA58447A
DeptSearchFields - EmpFileTest

(I put the database name in front of the table in case it looks
jumbled)

I need to import or insert or whatever it is called; one column of
data from a table in the DeptSearchFields database to a column of data
in a table in the AsureIDc51 database.

Both Database tables that I want to (for lack of a better word)
transfer between have an "employee ID" column.

AsureIDc51 - AF_Employee_Number

DeptSearchFields - Employee_ID

Both Database tables also have a Dept. Description column however

AsureIDc51 - AF_Search_Dept (empty)

DeptSearchFields - Dept_Desc (populated)

I want to create a script?/query?/ some sort of automated job that
will run once a week and do the following

Compare
DB - DeptSearchFields
Table - EmpFileTest
Column - Employee_ID

With
DB - AsureIDc51
Table - A_870211EE_7122_4AB3_B513_96B2AA58447A
Column - AF_Employee_Number

And then take
Column Dept_Desc from Database DeptSearchField Table EmpFileTest

and put it in Column AF_Search_Dept within Database AsureIDc51 Table
A_870211EE_7122_4AB3_B513_96B2AA58447A

where the employee ids match up.

This job will run multiple times so I need it to be created in such a
way that it will overwrite if an employee changes departments. Does
that make sense?

If so can anyone help me? I've tried to search google and the groups
but I don't know proper terminology.
Does this make sense?

I've got SQL Server Enterprise Manager

I've also got Visual Studio 2005 Professional if this helps with
creating whatever I need to create.

Thanks,
Ben

[Ed Murphy]

Ben wrote:

> I have 2 SQL Databases
>
> One is called DeptSearchFields
> The other is called AsureIDc51
>
> Within these 2 SQL databases there are tables (like you didn't know
> that already LOL)
> The tables I'm concerned with are:
>
> AsureIDc51 - A_870211EE_7122_4AB3_B513_96B2AA58447A

That's horrible. At least wrap it in a view:

create view insert_sane_name_here as
select * from A_870211EE_7122_4AB3_B513_96B2AA58447A

(I assume you know how to use Query Manager and/or Enterprise Manager
to apply this. If not, then you need full-on training, not just a spot
of help from the newsgroup.)

create procedure SyncDepartmentNames as
update AsureIDc51.dbo.A_870211EE_7122_4AB3_B513_96B2AA58447A
set AF_Search_Dept = eft.Dept_Desc
from AsureIDc51.dbo.A_870211EE_7122_4AB3_B513_96B2AA58447A a8
join DeptSearchFields.dbo.EmpFileTest eft
on a8.AF_Employee_Number = eft.Employee_ID

Then use Enterprise Manager to create a SQL Server Agent job that runs
the following, on whatever schedule you see fit:

exec SyncDepartmentNames