Data transfer into a new table of the target database

If radio button Into a new table of the target data source is checked on panel 3. Data transfer of page Enabled actions then this page looks as follows:

data_transfer-new_table

The two lists contain column names of the source table/view and the items GetDate() and GetUTCDate(). Names of fields you want to copy to the target database should be moved to the list Selected fields. Items under Available fields will be excluded from data transfer.

The data transfer step of a similar configuration is performed the following way:

•	Plugin loads data from the source table.

•	A new table is created in the target database. It will be named <name of source table>-<timestamp> and contain fields placed under Selected fields, in the same order. Their names and data types correspond to those of the columns in the source table.

•	Plugin iterates through each row of data read from the source and inserts the contents of selected fields into the newly created table as new records. when we select all the fields (but not GetDate() and GetUTCDate()) then this new table will be identical to the original one.

GetDate() and GetUTCDate() are used to add columns for timestamps. These will contain the time (local or UTC, respectively) when a record was inserted. Fields represented by them in the new table will be named Timestamp and Timestamp UTC (complemented by an ordinal number if required).

An example

Set up connection parameters of the source and target databases and select the desired source table. Let's assume this is called Registry and its contents are:

Registry

ID	Name	Year of birth
1	Jane Doe	1986
2	John Doe	1949
3	James Doe	1986

Lists on page Data transfer (3) contain the items ID, Name, Year of birth, GetDate() and GetUTCDate(). We would like the new table to contain data only from columns Name and Year of birth and the timestamps of records being inserted. Put items Name, Year of birth and GetDate() in the list Selected fields and ID and GetUTCDate() in Available fields to accomplish this.

After saving and executing the configuration - let's say this happened at 2010-06-13 09:58:10 - a new table called Registry-20100613095810 is created with the following contents:

Registry-20100613095810

Name	Year of birth	Timestamp
Jane Doe	1986	2010-06-13 09:58:10
John Doe	1948	2010-06-13 09:58:10
James Doe	1986	2010-06-13 09:58:10

Warning:

•	Names and data types of columns in the new table usually match the ones in the original table but other properties - such as keys and unique identifiers - are not inherited.

•	In some cases, data types of columns do not match those in the source table because of the diverse characteristics of data sources available through ODBC.

•	The plugin only inserts rows into the target table that are not present already. If we had selected Year of birth but not Name and GetDate() in the example above then the resulting table - having a single column called Year of birth - would have contained the value 1986 only once.