Wednesday, 9 November 2011

UPDATE table1 from table 2

I'm using a SQL Server staging area for my current ETL work and I find it easiest to update cleaned IDs using temporary tables that can be created and deleted as necessary. The syntax is simple and I use it in stored procs for persistent matched data and ad hoc queries for manual matching.

SET Table1.Text = t2.DifferentText
FROM Table2 t2
WHERE Table1.Id = t2.Id;

Because I use multiple databases within the same database server, I'm always careful to be specific about the database and table names. So, for example, if you're updating Table1 on Database1 with data from Table2 on Database 2 your script will look like this:

UPDATE Database1.dbo.Table1
SET Table1.Text = t2.DifferentText
FROM Database2.dbo.Table2 t2
WHERE Table1.Id = t2.Id;

No comments:

Post a Comment