Manage Learn to apply best practices and optimize your operations.

Utility routine: sp_ChangeParentValue

This routine transfers the relationship to child table rows from one parent table row to another.

The sp_ChangeParentValue routine transfers the relationship to child table rows from one parent table row to another.

The sp_ChangeParentValue stored procedure is useful for taking child rows found under multiple parent rows and combining them under a single parent row. Envision three customer rows that are found to be duplicates, where each customer row has several child rows in an order table. This routine can be used to combine all the order rows under a single customer row. Then the other two customer (parent) rows can be deleted.

    CREATE PROCEDURE dbo.sp_ChangeParentValue
        @DBTable varchar(100),
        @DBValue int,
        @DBAdmin int

The first parameter (@DBTable) specifies the parent table. The table must follow the rules of the surrogate key architecture suggested in my earlier article.

The second parameter (@DBValue) specifies the primary key IDENTITY value that acts as the source.

The third parameter (@DBAdmin) specifies the primary key IDENTITY value that acts as the destination.

The sp_ChangeParentValue stored procedure checks the child tables of @DBTable and changes the foreign key value @DBValue to @DBAdmin. The effect is to transfer relationship to (ownership of) the child rows from the @DBValue parent row to the @DBAdmin parent row.

This example uses the extra tables in my example database. It combines the child rows in the detail table under a single parent row.

Note: Be sure to DROP and CREATE the extra tables using the SQL code above before trying this example. The actions performed by sp_ChangeParentValue can be observed by checking the output of the surrounding SELECT statements.

   SELECT P.*
        , C.*
     FROM Example       AS P
LEFT JOIN ExampleDetail AS C
       ON P.ExampleID
        = C.ExampleID
    WHERE P.ExampleID IN (5,6,21)

EXECUTE sp_ChangeParentValue 'Example',5,21
EXECUTE sp_ChangeParentValue 'Example',6,21

   SELECT P.*
        , C.*
     FROM Example       AS P
LEFT JOIN ExampleDetail AS C
       ON P.ExampleID
        = C.ExampleID
    WHERE P.ExampleID IN (5,6,21)



Utilities to maintain a surrogate key architecture

 Home: Introduction
 Part 1: What are surrogate key values
 Part 2: T-SQL code to create stored procedures
 Part 3: Utility routine: sp_CheckRowCounts
 Part 4: Utility routine: sp_CheckKeyValues
 Part 5: Utility routine: sp_ResetNextKeyValue
 Part 6: Utility routine: sp_OrganizeKeyValues
 Part 7: Utility routine: sp_FindDuplicateRows
 Part 8: Utility routine: sp_ChangeParentValue

More advice:
Surrogate key architecture to perform powerful database operations
Framework to support a surrogate key architecture


ABOUT THE AUTHOR:   
Brian Walker
Brian Walker is a senior database architect in an IS department that uses SQL Server 2000 and the .NET Framework. He has more than 25 years of experience in the IT industry with the last several years focused on databases and SQL Server. Walker is a software developer, database developer, database administrator and database consultant. He develops utility software as a hobby, including a large collection of SQL Server utilities.
Copyright 2006 TechTarget
This was last published in March 2006

Dig Deeper on SQL Server Database Modeling and Design

PRO+

Content

Find more PRO+ content and other member only offers, here.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close