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.
If you want to learn how to Update a SQL Table with SmartConnect, watch this video.
