How to Add Multiple Instances of Table to Channel
Question
How to add multiple instances of a table to a channel?
Environment
HVR
Answer
In certain situations, e.g. when merging or splitting rows across tables, it is necessary to add multiple instances of the same base table to a channel.
The usual procedure using Table Select does not support adding a table multiple times. This would mean manually adding all columns as well, which is cumbersome especially when the table has lots of columns.
There are two methods to do this:
Method 1
- Right-click on Tables and select New Table;
- For Base Table Name enter the name of the table in the database (e.g. mytable), and for the Table Name enter the ‘alias’ for this table inside HVR.
- Pick a different name for each instance (e.g. using a sequence number: mytable1, mytable2). Repeat this until you have the number of instances you require.
- Go into the Table Select and connect to a location. The newly created tables are marked as Different Columns.
- Select all of them and click Replace. HVR now replaces the empty column list with the list retrieved from the database ‘describe’ of the table.
Method 2
Alternatively, you can use the HVR XML import/export functionality to achieve this:
- Add one instance of the table using Table Select in the usual way.
- Export the HVR catalogs into an XML file either from the GUI (right-click on the channel) or using command hvrcatalogexport.
- Open the XML file in a text editor and search for the definition of the table:
<hvr_table chn_name="mychannel" tbl_name="mytable" tbl_base_name="mytable"/>
- Copy this definition (changing the value for tbl_name) until you have the required number of instances:
<hvr_table chn_name="mychannel" tbl_name="mytable2" tbl_base_name="mytable"/> <hvr_table chn_name="mychannel" tbl_name="mytable3" tbl_base_name="mytable"/>
- Search for HVR's column definitions for this table:
<hvr_column chn_name="mychannel" tbl_name="mytable" col_sequence="1" col_name="prod_id" col_key="1" col_datatype="integer4"/> <hvr_column chn_name="mychannel" tbl_name="mytable" col_sequence="2" col_name="prod_price" col_datatype="decimal" col_length="10,2"/>
- Copy all column information for this table, each time replacing the tbl_name with the correct value until all of the added table instances have column information.
- Import the resulting XML catalog file either from the HVR GUI (right-click on hub) or using the command hvrcatalogimport.