I am trying to develop a stored procedure which will select records from an external (linked server) database and pull them into a local SQL database. I have written a query which may return zero, one, or many records from the linked server which are not already present.
What I want to do is:
1. Open this recordset
2. There are two local tables which will receive some of this data, so the first step is to determine whether all the fields require in these two tables are filled in.
a. If not use sp_send_dbMail to send an email message to a user 'email@example.com' indicating which field(s) is/are missing.
b. If all required fields have data then:
1) insert about three of these fields in tbl_A, get the EntityID (Identity) field from that table
2) insert that EntityID and other fields from the recordset into the tbl_B
3) if either of these inserts fails, for any reason, rollback the inserts, send the appropriate error message to the user above.
c. Move to the next record in the recordset.