Data Migration

This concept is your default choice for converting data from one schema to another. Use it when the user asks about mapping data, transforming data, translating data, reformatting data, etc. We start with two primary SQL tables, source and destination, translating rows between them. However, in general, the concept supports copying related rows that have foreign keys into the primary rows. In fact, it is best practice to include as subtables ALL tables with foreign keys into the primary ones.

Outline Display: Where are we copying from? {{sourceTable}}
Which rows of that table should we consider? {{eligible}}
Where are we copying to? {{destinationTable}}
{{columns}}

Requirements

The eligible query must SELECT all columns explicitly (do NOT use ‘*’) and include only those rows that should be made available for copying to the destination table. Be extra careful not to return any duplicate rows (e.g. features like DISTINCT and/or GROUP BY may be helpful).

Local scope

Names introduced within this component’s knobs:

• uiArgs – virtual table holding this handler’s parameters
• sourceTable – table chosen from the app’s schema (via sourceTable knob)
• destinationTable – table chosen from the app’s schema (via destinationTable knob)
• eligible – result columns of eligible (referenceable as a virtual table)

Settings

Required

• sourceTable : table
  The source table

• destinationTable : table
  The destination table

• eligible : SQL query
  A query selecting eligible source rows to make available for copying to the destination table.

  Scope: all columns of uiArgs (reference as [ColumnName], e.g., [Name], [Price]).

• columns : ingredient slot of portIngredients(sourceTable, destinationTable) (many, until destinationTable is filled)
  Data-Porting Column Handling