Retrieve Primary Keys using DML Operations
When you perform a DML operation on the Salesforce Marketing Cloud data store, you can use ODBC escape clauses to retrieve the primary keys that are associated with the rows that are manipulated.
- For an INSERT statement, the connector returns the primary key values for the newly inserted rows.
- For an UPDATE statement, the connector returns the primary key values for the updated rows.
- For a DELETE statement, the connector returns the primary key values for the deleted rows.
For example, the following DML statement uses an ODBC escape clause:
{return Id from 'UPDATE Category SET Name = ''UpdateinsertTest281117'' WHERE Name=''Asd'' '}
This DML returns a result set with one column and one row. This single cell contains the value of the primary key column that is associated with the new rows.
Note:
- Not all tables support this feature. If a DML statement refers to more than one table, the primary keys are returned only from the tables that can return a primary key. For example, if you INSERT into two tables but only one of the tables supports this feature, then only the primary key from that table is returned. For a list of the tables that can return a primary key, see the Simba Salesforce Marketing Cloud ODBC Driver with SQL Connector Reference Guide.
- If the response does not include all primary keys that are affected by the query, the connector returns an error.