Update a table from a temp table

I have a table that stores formulary drug information and needs to be updated daily from a central formulary. The temp table is identical to the drug table. The temp table data could be identical (and on most days will be) to the main table or it could have updated rows or new rows.

I have a stored procedure to update the main table, but it fails because it won't update NULL rows (if there is a new row in the temp table).

This is a MSSQL Server 2005.

Where am I going wrong here:

    -- Insert statements for procedure here
UPDATE [RX_Billing].[dbo].[FS_Drug]
SET [TRADENAME] = [RX_Billing].[dbo].[FS_Drug_TEMP].[TRADENAME]
  ,[CDM] = [RX_Billing].[dbo].[FS_Drug_TEMP].[CDM]
  ,[NDC] = [RX_Billing].[dbo].[FS_Drug_TEMP].[NDC]
  ,[IP_COST] = [RX_Billing].[dbo].[FS_Drug_TEMP].[IP_COST]
  ,[OP_COST] = [RX_Billing].[dbo].[FS_Drug_TEMP].[OP_COST]
  ,[HH_COST] = [RX_Billing].[dbo].[FS_Drug_TEMP].[HH_COST]
  ,[VAR_COST] = [RX_Billing].[dbo].[FS_Drug_TEMP].[VAR_COST]
  ,[LSTUPDATE] = [RX_Billing].[dbo].[FS_Drug_TEMP].[LSTUPDATE]
FROM [RX_Billing].[dbo].[FS_Drug]
RIGHT OUTER JOIN [RX_Billing].[dbo].[FS_Drug_TEMP] ON 
          [RX_Billing].[dbo].[FS_Drug].[TRADENAME] = 
                   [RX_Billing].[dbo].[FS_Drug_TEMP].[TRADENAME]

EDIT:

I went with Rory's code. Thanks, that works beautifully.

A note to Orion Edwards: UPSERT/MERGE is exactly what I wanted, but it is not supported under SQL Server 2005. Apparently it was planned, but didn't make that release. It is available in Server 2008. (From what the Interwebs has told me.)

From stackoverflow

• Wouldn't you want to do an INSERT on the new rows, and an UPDATE on existing rows? Updating this to an INNER JOIN, and adding a separate INSERT should resolve your issue if I'm understanding it correctly.

• You could try doing an UPSERT (which is basically "if exists, update, else insert").

  I believe SQL server calls it MERGE

• Standard way is to do an UPDATE and then an INSERT:

  -- UPDATE rows using an INNER JOIN with matching TRADENAME. No need to update TRADENAME column.
  UPDATE drug
  SET [CDM] = tmp.[CDM]
    , [NDC] = tmp.[NDC]
    , [IP_COST] = tmp.[IP_COST]
    , [OP_COST] = tmp.[OP_COST]
    , [HH_COST] = tmp.[HH_COST]
    , [VAR_COST] = tmp.[VAR_COST]
    , [LSTUPDATE] = tmp.[LSTUPDATE]
  FROM [RX_Billing].[dbo].[FS_Drug] drug
  INNER JOIN [RX_Billing].[dbo].[FS_Drug_TEMP] tmp  
      ON drug.[TRADENAME] = tmp.[TRADENAME]
  
  -- Insert rows that don't have matching TRADENAME
  INSERT INTO drug
  SELECT 
      tmp.[TRADENAME]
    , tmp.[CDM]
    , tmp.[NDC]
    , tmp.[IP_COST]
    , tmp.[OP_COST]
    , tmp.[HH_COST]
    , tmp.[VAR_COST]
    , tmp.[LSTUPDATE]
  FROM [RX_Billing].[dbo].[FS_Drug] drug
  RIGHT OUTER JOIN [RX_Billing].[dbo].[FS_Drug_TEMP] tmp 
      ON  drug.[TRADENAME] = tmp.[TRADENAME]
  WHERE drug.[TRADENAME] IS NULL
  

  You might also want to delete or flag as deleted any records in drug that aren't in tmp any more. Do that as a separate statement same as the UPDATE but with a LEFT OUTER JOIN where tmp.TRADENAME IS NULL.