Migration Export SQL

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>>