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.

Looking for more tech tips? View our related blogs on tech and development