Updating table on test SQL Server database from live environment with a query

I need to update a few tables on test environment from our production database. Basically it's the same table, just a few months more recent.

So, here's a query that I'm trying to implement:

UPDATE [Server-DEV].[Database].[dbo].[bHRRM] 
SET [Field1] = b.[Field1], 
    [Field2] = b.[Field2], ... ,
    [FieldX] = b.[FieldX]
FROM [Server-PROD].[Database].[dbo].[bHRRM] AS b

I don't care about data being overwritten, as it's the same. Even opposite is true - if there's a difference, test server better to update accordingly. But I don't want duplicates.

Now, there's a problem with a KeyID column, if it's included. And even if I'm excluding it from the SET - there's still an error:

Cannot insert duplicate key row in object 'dbo.bHRRM' with unique index 'biHRRM'. The duplicate key value is (5, 1000).

So, first question here is - what am I doing wrong?

Solution

The problem I see right now is there is nothing in the update query to correlate the PROD server data with the DEV server data. It's not enough for the two tables to have the same schema and key definitions: you need an explicit correlation as part of the UPDATE statement.

This means it's trying to set the values from every row in production to every row in dev. That's gonna be slow, but even more to the point: it's gonna create duplicates at various points.

What should work is this:

UPDATE dev
SET [Field1] = prod.[Field1], 
    [Field2] = prod.[Field2], ... ,
    [FieldX] = prod.[FieldX]
FROM [Server-PROD].[Database].[dbo].[bHRRM] AS prod
INNER JOIN [Server-DEV].[Database].[dbo].[bHRRM] dev on dev.KeyID = prod.KeyID

Alternatively, if this is some kind of full-refresh from production you might consider just dropping the DEV table to fully replace it (via SELECT INTO). (This will also mean redefining any indexes/triggers/etc). Or you could TRUNCATE the DEV table to efficiently remove all of the records but preserve the structure, then do an INSERT ... SELECT with no WHERE to repopulate data from production.

Of course, if it's not a full refresh these options could remove important older rows and insert new rows you don't want (and then just the UPDATE query shown is enough). But if it is, this also takes care of those operations to either simplify the old process or cover something that was previously missed.