We are trying to update a SQL Table using the SQL Table Destination in SmartConnect and continue to get a ‘Violation of unique key constraint Cannot insert duplicate key.’ We do not get this error updating other SQL tables using this method. We also have the Update Existing checkbox marked so it should be able to update that existing record.
When using the SQL Table destination in SmartConnect, the table must have a Primary Key Constraint assigned so SmartConnect can determine which record to update. If there is no Primary Key, SmartConnect will create an update statement that will attempt to update all records in the table.
If there is no Primary Key that can be created on the table, the next method would be to create a SQL Stored Procedure to update the table, writing the necessary logic for the update. Then use the SQL Stored Procedure destination in the SmartConnect map.
http://www.eonesolutions.com/Manuals/SmartConnect/SmartConnect%202016/?page=sql_microsoft_sql_stored_proce
http://www.eonesolutions.com/Manuals/SmartConnect/SmartConnect%202016/?page=sql_microsoft_sql_table_destin
