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

Question

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

Both databases have the same structure, but different data. Database1 (Test1) is the last, 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 a table in Test1 from the same table in Test2 for a specific column called DeptID because the values ​​in the Employee table in Test2 database (old) have been updated. Therefore, I need to update the table in the new database from the table in the old database, which has about 1000 rows.

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

I know that I can restore the database itself, but this is not a solution. I need to update the values ​​in the Test1 database from the Test2 database.

Answer 1

If two databases are on the same server, you should create an 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 don’t quite understand if you want to update Test1.dbo.Employee values ​​from Test2.dbo.Employee (which makes my request) or vice versa (since you mention that db on Test1 was a new table ...... )

Answer 2

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

Answer 3

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

`

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

`

Answer 4

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

you cannot use from keyword for mysql

Answer 5

Try a query like

 INSERT INTO NEW_TABLENAME SELECT * FROM OLD_TABLENAME; 

Answer 6

it works wonders - it’s not his turn to call this procedure form code using a DataTable with a scheme that exactly matches custType to create a table client (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); } } } 

Answer 7

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

Answer 8

use test1

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