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 T开发者_开发百科est2 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.

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

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

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

Try a query like

INSERT INTO NEW_TABLENAME SELECT * FROM OLD_TABLENAME;

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);
                }
            }
}

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

use test1

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