What is the safest way to merge 2 table rows in SQL Server?

Question

I have a table called [Sectors], which stores industry sectors. [SectorId] is defined as an INT and is the primary key of this table. These sectors are referenced throughout the database using the primary key, but there are no foreign key constraints for this primary key in the other tables.

Now there are 2 sectors in this table that now need to be merged into one. We have Sector X and Sector Y. Sector Y needs to be merged into Sector X. So basically I need to replace all the references to Sector Y in the other tables with a reference to Sector X, and then delete Sector Y from the [Sectors] table.

The problem is that with no foreign key constraints, I could end up missing some tables that still reference Sector Y.

What's the best way of doing this?

Answer 1

Can you add foreign keys with on update cascade semantics?

Regarding your point

I could end up missing some tables that still reference Sector Y.

There is no magical way that SQL Server can know this either in the absence of FK constraints. You could look for similarly named columns in the Information_Schema views or at the definition of the database dependencies (stored procedures, views) of the Sector table but neither approach is remotely infallible.

Answer 2

If you haven't called the field SectorID in all tables, you can loop through all tables that has an integer field and checking if any "Sector Y" records exists.

You can do this by joining syscolumns with sysobjects (WHERE xtype = 'U' for user-table).

Answer 3

SET NOCOUNT ON

DECLARE 
  @SQL AS NVARCHAR(MAX),
  @name AS NVARCHAR(128)

SELECT name
    INTO #tables
    FROM sys.sysobjects AS O
    WHERE EXISTS (SELECT *
                    FROM sys.syscolumns
                    WHERE id = O.id
                      AND name = 'SectorID')

WHILE EXISTS (SELECT * FROM #tables)
BEGIN
    SELECT TOP 1
      @name = name
        FROM #tables

    SET @SQL = 'IF EXISTS (SELECT * FROM ' + @name + ' WHERE SectorID = 2)' + CHAR(13) + CHAR(10)
    SET @SQL = @SQL + 'BEGIN' + CHAR(13) + CHAR(10)
    SET @SQL = @SQL + ' UPDATE ' + @name + ' SET SectorID = 1 WHERE SectorID = 2' + CHAR(13) + CHAR(10)
    SET @SQL = @SQL + 'END' + CHAR(13) + CHAR(10)
    PRINT @SQL

    DELETE
        FROM #tables
        WHERE name = @name
END

DROP TABLE #tables