Due to scheduled maintenance, some areas of our site are currently inaccessible. Thank you for your patience.

Please reach out to sales@eonesolutions.com with any questions.

Back

SQL Table destination in SmartConnect will not update existing records

Published: Feb 01, 2022
Post Author Written by David Youngquist

Running a SmartConnect map and using the SQL table destination.  The import is set to “Update Existing” however it is not updating the existing records, it keeps inserting new records.  


In order to update existing records when using the SQL table destination, the following 4 conditions need to be met.  

1.  The SQL table must have a key field configured in the table. If there is not a key field defined in the destination table, then updates are not possible and maps will always insert a new row.

2.  The key field in the SQL table CANNOT be an identity column (a column that automatically populates like DEX_ROW_ID does in GP)

3.  You MUST map a source field to the key field in the SQL table. You can’t use a rolling column, or a script or a constant, you have to map a field FROM the source file to the key field in the SQL table.

4.  When you configure the data source, you select the source field you have mapped as the “Key Field” in the data source.

If all 4 conditions are met, then the map will automatically do inserts and updates (if “Update Existing” is marked) based on the key fields. If the above 4 conditions are not met, then any imports will always append a new record to the table instead of updating an existing record.

Feeling stuck? Get the support and guidance you need to help you power through any data challenge

We're on your integration team. Connect with our people and let us know how we can help you.