Skip to main content

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 NameObjects and FieldsNULL / PlaceholdersStatic Value (strings and literals)
SnowflakeDouble QuotesNULLSingle Quotes
SQLBrackets [] or Double QuotesNULLSingle Quotes
MySQLBackticks ` or otherwise can be set to “None”NULLSingle Quotes
POSTGRESQLDouble QuotesNULLSingle 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.


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

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