[sql-server-2005] How to update data in one table from corresponding data in another table in SQL Server 2005

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.

UPDATE table1
SET column1 = (SELECT expression1
               FROM table2
               WHERE conditions)
[WHERE conditions];

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

you can not use from keyword for the mysql

 UPDATE Employee SET Empid=emp3.empid 
 FROM EMP_Employee AS emp3
 WHERE Employee.Empid=emp3.empid

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

use test1

insert into employee(deptid) select deptid from test2.dbo.employee

this works wonders - no its turn to call this procedure form code with DataTable with schema exactly matching the custType create table customer ( id int identity(1,1) primary key, name varchar(50), cnt varchar(10) )

 create type custType as table
 (
 ctId int,
 ctName varchar(20)
 )

 insert into customer values('y1', 'c1')
 insert into customer values('y2', 'c2')
 insert into customer values('y3', 'c3')
 insert into customer values('y4', 'c4')
 insert into customer values('y5', 'c5')

 declare @ct as custType 
 insert @ct (ctid, ctName) values(3, 'y33'), (4, 'y44')
 exec multiUpdate @ct

 create Proc multiUpdate (@ct custType readonly) as begin
 update customer set  Name = t.ctName  from @ct t where t.ctId = customer.id
 end

public DataTable UpdateLevels(DataTable dt)
        {
            DataTable dtRet = new DataTable();
            using (SqlConnection con = new SqlConnection(datalayer.bimCS))
            {
                SqlCommand command = new SqlCommand();
                command.CommandText = "UpdateLevels";
                command.Parameters.Clear();
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@ct", dt).SqlDbType = SqlDbType.Structured;
                command.Connection = con;
                using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
                {
                    dataAdapter.SelectCommand = command;
                    dataAdapter.Fill(dtRet);
                }
            }
}

Try a query like

INSERT INTO NEW_TABLENAME SELECT * FROM OLD_TABLENAME;