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

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 prim开发者_JS百科ary 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?

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

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

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.

I made some work to find a solution for very similar question. The result is the stored procedure that takes IDs of merging records, leaves first of them, updates all foreign keys in related tables and deletes the rest.

For example about topicstarter question. Let's assume we have theese tables:

[Sectors]
ID Name
10  'SectorA'
20  'Sector A'
30  'Sector B'
40  'sector a'

[RelatedRecords]
ID, SectorID, SomeField
1,  10        'value 1'
2,  20        'value 2'
3,  30        'value 3'
4,  40        'value 4'

(ID must be the primary key, SectorID must be the foreign key) and we want to merge records 10, 20, 40 leaving record 20. To do this we should call:

dbo.MergeRecords '20, 10, 40', 'Sectors'

and the result will be:

[Sectors]
ID Name
20  'Sector A'
30  'Sector B'

[RelatedRecords]
ID, SectorID, SomeField
1,  20        'value 1'
2,  20        'value 2'
3,  30        'value 3'
4,  20        'value 4'

If there are no related tables then only deletion will be performed. This solution covers the case when you have single-valued primary key (3NF as I remember).

So here is the stored procedure code:

-- =============================================
-- Description: Merging table records.
-- First record will be leaved, other will be deleted.
-- Depended foreign keys in all tables will be updated.
-- Example:
-- exec MergeRecords '1, 2, 3', 'SomeRecords'
-- =============================================
CREATE PROCEDURE [dbo].[MergeRecords]
    @Id nvarchar(max),      -- Comma-separated IDs
    @PKTable nvarchar(50)   -- Name of a table where merge records in
AS
BEGIN
    SET NOCOUNT ON; 

    declare @PKField nvarchar(50),
            @FKTable nvarchar(50),
            @FKField nvarchar(50)

    declare @updateSql nvarchar(max),
            @deleteSql nvarchar(max)

    declare @firstId nvarchar(max),
            @otherId nvarchar(max)

    set @firstId = LEFT(@Id, CHARINDEX(',', @Id) - 1)
    set @otherId = RIGHT(@Id, LEN(@Id) - CHARINDEX(',', @Id))

    -- Primary key name
    select @PKField = ccu.COLUMN_NAME 
        from INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
        join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu on ccu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
        where tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
        and tc.TABLE_NAME = @PKTable

    -- Loop foreign keys
    declare constraints_cursor cursor local fast_forward
    for select 
            --tc.CONSTRAINT_NAME, 
            --ccu_pk.TABLE_NAME PK_TABLE_NAME, 
            --ccu_pk.COLUMN_NAME PK_COLUMN_NAME, 
            ccu_fk.TABLE_NAME FK_TABLE_NAME, 
            ccu_fk.COLUMN_NAME FK_COLUMN_NAME

        from INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc 
        join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc on rc.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
        join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu_fk on ccu_fk.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
        join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu_pk on ccu_pk.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME

        where ccu_pk.TABLE_NAME = @PKTable
        and tc.CONSTRAINT_TYPE = 'FOREIGN KEY'

        --Example, @PKTable = 'SomeRecords'
        --CONSTRAINT_NAME                           PK_TABLE_NAME   PK_COLUMN_NAME  FK_TABLE_NAME               FK_COLUMN_NAME
        --FK_SomeRecords_SomeRelatedRecords1        SomeRecords     Id              SomeRelatedRecords          FirstSomeRecordId
        --FK_SomeRecords_SomeRelatedRecords2        SomeRecords     Id              SomeRelatedRecords          SecondSomeRecordId
        --FK_SomeRecords_AnotherRelatedRecords      SomeRecords     Id              AnotherRelatedRecords       SomeRecordId

    open constraints_cursor 
    fetch next from constraints_cursor 
    into @FKTable, @FKField

    while @@fetch_status = 0
    begin
        -- Update foreign keys
        set @updateSql = '
            update @FKTable
            set @FKField = @firstId
            where @FKField in (@otherId)'

        set @updateSql = replace(@updateSql, '@FKTable', @FKTable)
        set @updateSql = replace(@updateSql, '@FKField', @FKField)
        set @updateSql = replace(@updateSql, '@firstId', @firstId)
        set @updateSql = replace(@updateSql, '@otherId', @otherId)
        exec sp_executesql @updateSql

        fetch next from constraints_cursor 
        into @FKTable, @FKField
    end

    close constraints_cursor
    deallocate constraints_cursor 

    -- Delete other records 
    set @deleteSql = 
        'delete from @PKTable
        where @PKField in (@otherId)'

    set @deleteSql = replace(@deleteSql, '@PKTable', @PKTable)  
    set @deleteSql = replace(@deleteSql, '@PKField', @PKField)
    set @deleteSql = replace(@deleteSql, '@otherId', @otherId)
    exec sp_executesql @deleteSql

    select 0    
END