Merge Duplicate Computer Records in Deployment Server

Occasionally, the Deployment Server console will show multiple computers with the same computer name. I have created a SQL Query that will merge these duplicate records. To use this SQL Query, open SQL Query Analyzer or Management Studio, create a new query, select your Deployment Server Database (eXpress) as the active database from the drop-down list of databases, copy and paste the following query text into the Query Window, and then execute the query. Everything following a “--” is a comment to help you understand what it does.

--Create a table variable to pair duplicate records for merging
DECLARE @ComputerIDs TABLE
(
  NewCompID int,
  OldCompID int
)
--Find all computers with more than one ID. The larger number is the younger computer record, the smaller number is the older computer record.
INSERT INTO @ComputerIDs (NewCompID, OldCompID)
  SELECT MAX(computer_id) AS NewCompID, MIN(computer_id) AS OldCompID
    FROM computer
    GROUP BY computer_name HAVING COUNT(1) = 2
 --Now update the younger computer record with all the data from the old computer record
--Get the Group ID from the old computer record UPDATE N
  SET group_id = O.group_id
  FROM computer N
    JOIN @ComputerIDs I ON N.computer_id = I.NewCompID
    JOIN computer O ON I.OldCompID = O.computer_id
--Delete location from the new record DELETE FROM location WHERE computer_id IN (SELECT NewCompID FROM @ComputerIDs)
--Delete service processors from the new record DELETE FROM service_processor WHERE computer_id IN (SELECT NewCompID FROM @ComputerIDs)
--Move location from the old record to the new record UPDATE T SET computer_id = I.NewCompID
  FROM location T
    JOIN @ComputerIDs I ON T.computer_id = I.OldCompID
--Move service processors from the old record to the new record UPDATE T SET computer_id = I.NewCompID
  FROM service_processor T
    JOIN @ComputerIDs I ON T.computer_id = I.OldCompID
--Move config items from the old record to the new record UPDATE T SET target_computer_id = I.NewCompID
  FROM config_item T
    JOIN @ComputerIDs I ON T.target_computer_id = I.OldCompID
--Move event schedules from the old record to the new record UPDATE T SET computer_id = I.NewCompID
  FROM event_schedule T
    JOIN @ComputerIDs I ON T.computer_id = I.OldCompID
--Move history from the old record to the new record UPDATE T SET computer_id = I.NewCompID
  FROM history T
    JOIN @ComputerIDs I ON T.computer_id = I.OldCompID
UPDATE T SET computer_id = I.NewCompID
  FROM history_archive T
    JOIN @ComputerIDs I ON T.computer_id = I.OldCompID
--Move status log from the old record to the new record UPDATE T SET computer_id = I.NewCompID
  FROM status_log T
    JOIN @ComputerIDs I ON T.computer_id = I.OldCompID
UPDATE T SET computer_id = I.NewCompID
  FROM status_log_archive T
    JOIN @ComputerIDs I ON T.computer_id = I.OldCompID
--Delete old computer records DELETE FROM computer WHERE computer_id IN (SELECT OldCompID FROM @ComputerIDs)