This will depend on what the appropriate instruction is on MS SQL to gather the auto-generated key value.
To be clear: using Pega to directly manipulate data in another database using RDB-* operations has been against Pega's best practice for a long time. As you have found, you have to:
Code SQL-specific transaction management
Use database-vendor-specific commands to solve problems like retrieving auto-generated key values
Mapping the external database table to a Pega class is preferable to the above, but even that does not solve all of these problems.
The recommended pattern to use for solving this sort of problem is have the owner of this database provide a REST API for Pega to interface with for entity manipulation. A REST POST would then more appropriately delegate the responsibility of providing the key of the newly created record on the service provider by requiring it be supplied in the response of the POST request.