How to update data in one table from corresponding data in another table in SQL Server 2005

Question

I have two tables in different databases on the same database server.

Both the databases have the same structure, but different data. Database1 (Test1) is the latest, and database2 (Test2) is an old copy of the database.

• Test1 has a table called Employee with 3000 records
• Test2 has a table called Employee with 1000 records

I need to update the table in Test1 from the same table in Test2 for a particular column called DeptID, because the values in the Employee table in the Test2 DB (the old one) have been updated. So I need to update the table in the new DB from the table in the old DB which has around 1000 rows.

In other words, I need to update the DeptID column in the Employee table in the Test1 DB with whatever values I have in the DeptID column in the Employee table in the Test2 DB.

I know I can restore the DB itself, but that's not a solution. I need to update the values in the Test1 database from the Test2 database.

Answer 1

If the two databases are on the same server, you should be able to create a SQL statement something like this:

UPDATE Test1.dbo.Employee SET DeptID = emp2.DeptID FROM Test2.dbo.Employee as 'emp2' WHERE    Test1.dbo.Employee.EmployeeID = emp2.EmployeeID 

From your post, I'm not quite clear whether you want to update Test1.dbo.Employee with the values from Test2.dbo.Employee (that's what my query does), or the other way around (since you mention the db on Test1 was the new table......)

Answer 2

update t2 set t2.deptid = t1.deptid from test1 t1, test2 t2 where t2.employeeid = t1.employeeid