Table Storage locations can be changed through the Storage tab on a table's Properties dialog, but they can also be changed in bulk by using the following process:
- Double-click on the desired object group in the left pane. This will display all the tables in that group in the middle pane.
- Select the tables that you wish to change the storage for using standard Windows selection.
- Right-click to bring up a menu and select Storage.
- On the Target Location Selection dialog, select the desired Target Location for the change.
- Double-click on the desired object group in the left pane. This will display all the tables in that group in the middle pane.
- Double-click on the desired object group in the left pane. This will display all the tables in that group in the middle pane.
Note
The Force Target Usage setting in the Tools > Options > Storage > Target Usage removes the local target option from the drop-down list. Refer to Target Usage for details.
- Follow the next dialogs to complete the bulk storage change.
- Please note that all procedures from the affected tables will need to be manually changed or regenerated after a bulk storage change.
If the database type does not support moving tables, such as Oracle, Greenplum, Netezza, Teradata and PDW, all affected tables will also need to be manually recreated after any storage changes.
This also applies to moving SQL Server tables between databases, since SQL Server only support moving tables between schemas, within the same database.
The tables moved are dropped from the source database but their metadata are stored in the destination database. The tables must be recreated in the destination database.Warning
Please note that changing the Storage for Dimension and Fact tables need to be handled very carefully, because artificial key relationships between Dimension and Fact could become out of sync.Recreating Fact tables and large Dimension tables might take a considerable amount of time.