Process records via T-SQL cursor

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 '' 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.
LVL 52
Dale FyeOwner, Developing Solutions LLCAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PadawanDBAOperational DBACommented:
Why do you want to use a cursor ?  You should be able to do this with set based logic.  Are you having trouble with any of these parts in specific or the whole kit 'n' caboodle ?
Dale FyeOwner, Developing Solutions LLCAuthor Commented:
I'm an Access MVP, but my SQL Server experience is generally limited to views and SELECT, UPDATE, APPEND, DELETE, and MERGE SQL statements.

I'm not sure which method is more efficient or easier to write,  but given the limited number of records which will be processed (5-10) at a time, I assumed it would be more efficient to do this as a cursor.  But I'm open to any suggestions.

In Access, I'm partial to set based operations, but the key is that I need to send the emails if required fields are missing or if an error is encountered for either of the inserts, and need to specifically identify which record had missing fields or failed the insert operation.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PadawanDBAOperational DBACommented:
No problem at all!  So what I would probably recommend is set-based where you:

Step 1) Gather all records with missing values for the fields that are required and email those to the user
Step 2) Begin transaction
Step 3) Begin a try...catch block
Step 4) Insert the column values from the rows with all required values (excluding the ones from Step 1, essentially) into table A
Step 5) Insert the column value(s) from the rows with all required values (you want to again exclude the ones from Step 1)
Step 6) Commit transaction
Step 7) In the catch block, put in the email to the user with the error message and rollback the transaction

I just think the rollback/commit is cleaner with set-based, but that's probably personal preference on my part.  With 5-10 records at a time, the performance isn't going to be such a stark difference, but hey - never know.  If you give some column names/table names (columns with required values and what column values are moving to which tables), I'd be happy to help with specifics.
Dale FyeOwner, Developing Solutions LLCAuthor Commented:
After doing some additional research, I ended up working through the cursor process, and it is working well.  May not be the most efficient, but is exactly what I was looking for.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.