DataTools Pro migration tab prepares SQL preview of your mapped data to help expedite translating your mapping to code for ETL (extract, transform, load).
Background: Typical data migrations with a larger number of source tables, or systems with complex source schemas require staging the data and transforming it. Even Salesforce to Salesforce data migrations can require complex join transformation functions. To help translate data mapping to executable code DataTools Pro features a SQL export:
Export to SQL
Export to SQL
1. Navigate to the Migration tab
2. Click the “Export Database SQL” icon
Within a popup modal window you will see a SQL code representation of your data mapping.
3. Select the appropriate syntax options based on the database you are exporting to.
Syntax Option Examples:
| DB Name | Objects and Fields | NULL / Placeholders | Static Value (strings and literals) |
| Snowflake | Double Quotes | NULL | Single Quotes |
| SQL | Brackets [] or Double Quotes | NULL | Single Quotes |
| MySQL | Backticks ` or otherwise can be set to “None” | NULL | Single Quotes |
| POSTGRESQL | Double Quotes | NULL | Single Quotes |
4. Click the “Copy to Clipboard” to copy the code for use in your database management or ETL tool.
Multiple Objects Mapping Impact to SQL
In the event your data source has multiple source objects that map into a single Salesforce object, you have flexibility to map each source object independently inside of DataTools Pro. As a result, DataTools Pro will pre-fix the mapped field based on the source object.
Tips
When performing data migrations, we typically use the output code inside of a CTE, Common Table Expression. A CTE is a result set of a query which exists temporarily and for use only within the context of a larger query. This allows your ETL developers to perform joins and implement transformations independent of your data mapping. In addition to the DataTools Pro generated SQL code, we typically hand off the following to demonstrate to our ETL developer perform their data transformations and joins. Here is a typical flow of information.
As new transformed fields are introduced you can update your source data fields / schema inside of DataTools Pro.
WITH DataProcessingCTE AS (
-- Perform the complex data transformations here.
-- This can include joins, aggregations, case statements, etc.
-- This example is simplified and should be expanded based on your data transform needs
SELECT *
-- Select from your source table in your database
FROM
<<YOUR DATABASE>>.<<YOUR SCHEMA>>.<<YOUR SOURCE TABLE>>
),
<<OBJECT NAME FROM DATATOOLS SQL>> AS (
-- Optional: Any additional transformations that build on the previous CTE. Possibly fine tuning, renaming or calculated logic.
SELECT *
-- Further processing based on the previous CTE
FROM DataProcessingCTE
)
-- Data Tools Generated SQL code intended to only map values as defined by DataTools.
-- <<PASTE DATATOOLS PRO SQL OUTPUT HERE>>
