Denormalize a table that uses lookup values?

Question

I have two tables. Table 1 is a basic "contact" style table with various personal columns. One of the fields in Table 1 is a lookup column that references Table 2. I want to either create a new column or set an empty column in Table 1 with the actual lookup values from Table 2. What is the most efficient way to do this in MSSQL such that processing time, i.e for a large number of rows, is minimized?

Answer 1

There aren't many facts to use, but after adding a new column, you can use a cte to establish values into that column.

ALTER TABLE table1
ADD newcol varchar(50);

WITH
      cte AS (
                  SELECT
                        table1.id
                      , table1.newcol
                      , table2.name AS newcol_value
                  FROM table1
                  INNER JOIN table2 ON table1.table2_fk = table2.id
            )
UPDATE cte
SET newcol = newcol_value
;

My guess is you are hoping this will improve performance somewhere, but you will now need to maintain this new column which will cost you in other ways.

nb: test it first, have a backup, etc.

Answer 2

The following code works:

update dbo.MyContacts 
   set StateAbbreviation = S.StateAbbreviation
  from dbo.MyContacts C
inner
  join States S
    on C.State = S.StateId

Answer 3

The correct syntax should be:

update c
   set StateAbbreviation = S.StateAbbreviation
   from dbo.MyContacts C inner join
        States S
        on C.State = S.StateId;

Note the use of the alias for the update. If you use the table name, all rows in the table will be updated with arbitrary values because there is no link between the table being updated and the tables referred to in the FROM clause.