Create Remote Table on Denodo to Migrate Data from Source to Target Database

Data architects and data professionals can move data from its source into a target database table using Denodo's remote table feature. SQL developers can use Denodo Virtual DataPort and VQL Shell where they can build their SQL query to read data from its data source. This VQL query is used to build a base view on Denodo and a new database table is created on the selected remote data source. Executing the remote table creation task populates the remote table with data fetched by VQL query. The Denodo Remote Table feature can be used to migrate data from its source to a target destination table.

In this Denodo tutorial, I want to show how SQL developers can use Remote Table feature for data transfer in addition to its main task as a data virtualization tool.

Log on to Denodo Virtual DataPort Administration Tool

Right click on a database node in Server Explorer window, then "New > Remote Table"

Here is the initial view for the "Create Remote Table" template screen

First select the target data source.
Select the "Database" name from the dropdown list. The list consists of the databases seen within Server Explorer windows on the left. These are the logical databases created on Denodo Virtual DataPort.
Following database name selection, the "Name" combobox will be enabled and populated with data sources defined in the previously selected Denodo database.

We continue with Remote Table section.
First make your selection from Catalog
Since the data source which I selected in previous step was a SQL Server, in catalog combo, I see the list of databases on the SQL Server instance.

After the Catalog selection, the schema dropdown list is populated. All schemas created under the chosen SQL Server database is listed here.

All these selections up to now, enables the Denodo developer to define the remote data source, database or catalog, schema where the new remote table will be created.
Actually we are identifying the hierarachy of the remote table on its data source.

Now type the name of the target remote table which you want to create using Denodo Virtual DataPort.
Let me name my remote table as "myNewTable"

While Denodo creates the remote table on its data source, it also creates a base view for it, too.
So in Base View section, you can define what will the name of the base view be, in which Denodo database you want to create it, and in which folder you want to see it in.

Now, the Denodo developer is ready to define the source of the data that will be queried or read from its source and written into target remote table.

For data of the remote table, Denodo developer will use the VQL Query.
To select the data let's first build your source SQL or VQL query on a new VQL Shell screen.

I want to use the data from an SAP data source T001 Compnay Code database table. Here is my SQL query

After you build your query and add/remove fields, etc you can use the VQL query on the Create Remote Table screen.

Press Create button.

The creation finished successfully.
Step 1 of 3: Created remote table 'myNewTable' successfully.
Step 2 of 3: Inserted 264 rows into remote table 'myNewTable'.
Step 3 of 3: Created base view 'bv_myNewTable' successfully in the 'poc' database.

Click OK to close, Open base view to see the base view created for generation of the data for this data migration task.

If you launch a SQL Server Management Studio SSMS and connect to the target data source, our SQL Server PCS database, under Tables node we can view our table and query its data using a simple SELECT query

As seen above, the data from SAP HANA database table is migrated to SQL Server database table using Denodo Remote Table feature easily.

SQL data professionals can use Denodo as a data abstraction layer aka data virtualization layer and as well as an ETL tool for migrating data from its source into a target database table.