Update Data from One Database to Another with Both Tables Containing Unique References
Friday, November 21, 2014
While researching another SQL Server-related issue, I found many people looking to update the data from one database table to another database table with both tables containing unique references. I thought to share my experience where I had to copy the Customer Email Addresses along with some other information from the QA Environment Database to the Performance Testing Environment Database.
Here is how I did that:
SET cust.ContactEmail = originalCust.ContactEmail
FROM DBName1.dbo.Customer cust, DBName2.dbo.Customer originalCust
WHERE cust.custID = originalCust.custID
The statement above is written very similarly to the SELECT statement-where we join two or more tables to retrieve data.
In this example:
- Instead of SELECT, we have an update statement.
- Instead of SELECT Columns we have SET columns that update the data automatically.
Comments are welcome!