Splitting a table - SQL Prompt

SQL Prompt enables you to split a table in your database into two tables.

Splitting a table can be useful if you want to:

normalize a database
For example, by moving columns to a new secondary table to avoid insert anomalies.
improve database performance
For example, by moving large but rarely used columns to a secondary table.
change the database design
For example, to enable storing multiple customer addresses.

Note: Splitting a table is available only in SQL Prompt Pro Edition.

Example: splitting a simple table

The following example demonstrates how SQL Prompt splits a table. In the example, we want to split a table with three columns, Name, ID and Category:

SQL Prompt 5.0 Help Linked GIF File Template

In the table, the value of ID determines the value of Category. When splitting the table, we can use SQL Prompt to:

copy ID to a new secondary table
move Category to a new secondary table

Preparation

If you copy any of the following to a secondary table, the split table script will fail:

XML columns
Columns that allow NULL values
Timestamp columns
Partitioned columns

Note that DML triggers that access data in the primary table are dropped when the table is split. You are strongly recommended to back up these triggers prior to launching the split table wizard.

For more detailed information, see Preparing the table in detail.

Splitting the table

To split a table:

In the Object Explorer pane, right-click the table you want to split, and then click Split Table.
The Split Table wizard is displayed:
In Secondary table name, type a name for the new secondary table.
You must enter a name that is not already in use.

If required, you can also select a new owner for the table.
Click Next.
To assign columns from the primary to the secondary table, select the column(s) and click the Copy > or Move > buttons.
Note that:
- you must copy at least one column to the secondary table so the tables can maintain a relationship
- you cannot move primary key columns to the secondary table
- you can select multiple columns to move, copy, or remove using Ctrl or Shift
To remove an assigned column from the secondary table, select the column and click < Remove.

To remove all of the assigned columns from the secondary table, click Reset All.
If required, reorder the assigned columns in the secondary table using the and buttons.
You can reorder multiple columns simultaneously.
Click Next.

By default, a foreign key is created on the primary table, to reference the primary key on the secondary table. This is for 1:1 or m:1 relationships.

If you have copied all the primary key columns from the primary table to the secondary table, you can choose to create the foreign key on the secondary table, to reference the primary key on the primary table. This is for 1:1 or 1:n relationships.
Click Next.
SQL Prompt creates the split table script and displays summary information:
- Actions is a summary of the actions that the script will perform, in the order in which they will occur.
- Warnings displays information that you should consider prior to running the script, including any reasons the script might fail.
- Dependencies lists objects that will be modified by the script because they reference, or are referenced by, the primary table.
The actions and warnings are included in the split table script, below the header. If you do not want to include this information in the script, clear the Include summary in the script check box.
Click View Script.
The wizard is closed, and the split table script is displayed in a new SQL Server Management Studio query window.

You are recommended to back up your database before you run the script.

For detailed information describing what happens to your objects when you run the script, see What happens when I run the split table script?
Once you have reviewed the script, run it.