Update Data from Two Databases with Unique References
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:
UPDATE cust 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.