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