The Data Transport component can Insert/Update/Delete data between different data sources.
|Data sources include|
In this example I will copy data from an external ODBC data source into the a new table in the local Firebird repository which in it self is a full featured SQL database.
The 3 steps are:
- Create a database connection to the source data.
- Create a Data Transport component and specify source and target.
- Run the component and see the result.
Step 1: Create a database connection
Give the connection a short name and specify connection parameters. Then test the connection to make sure the configuration is ok. Note that the password is in clear text. You can use password encrypted parameters this field and dynamically change password without having to go through numerous connections.
Step 2: Create a Data Transport component.
Specify your source, in this example we will use the database connection just created, and write the SQL needed. You can use the visual SQL query designer to select tables and where clause etc.
Now on the “Target tab” select “Database or function” and leave the connection
as “Current repository” This will transfer the data to the built in Firebird Repository database.
Then R-Click to paste in a SQL Command template generated form the source.
The generated SQL will do the following:
- Drop an existing table with the name ‘MyTable’ and ignore any errors if it does not exist.
- Create a new table with the right definition as *MyTable’
- Insert rows into the table based on the data set from the source.
(Note that the Source also can be SharePoint)
The UPDATE statement, commented out, is just an example on how to update tables with data from the Source.
Step 3. Save and run the component