Update a table from a temp table

Question

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.)

Answer 1

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.

Answer 2

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

I believe SQL server calls it MERGE

Answer 3

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.