After explaining basic principles for replacing one-to-many relationships with associative tables in Part 1 of the article, this sequel will show you how to easily accomplish that via the Replace one-to-many relationship feature.
The Replace one-to-many relationship command is located under the ApexSQL -> ApexSQL Refactor submenu integrated in SQL Server Management Studio and Visual Studio:
The feature can also be accessed via the context menu, as well. Right-click the target table and select the Replace one-to-many relationship command from the ApexSQL Refactor menu:
After that, the Replace one-to-many relationship dialog will be displayed:
In this dialog the dependent table, the relationship and a foreign key reflecting the one-to-many relationship that will be replaced, and the name for the associative table need to be specified.
The Preview option will show the SQL script to be executed; it will be displayed under the Generated script tab:
The Warnings tab displays potential problems that can occur when using the Replacing one-to-many relationship refactor:
The Sequence tab contains summarized operations and their execution order. The execution sequence is generated in the specific manner that prevents dependency problems:
Select the Dependencies tab to preview dependent objects, which will be updated automatically to reflect the change:
Replacing one-to-many relationships is easy with ApexSQL Refactor.
February 27, 2015