What is the best way to call stored proc for each row?

I try to copy this set of tables to other set with the same scheme as the source.

I wrote stored proc, in SQL, that receives ID from TableA and copies all tables from B-G. Now I want for each row of TalbeA to call that stored proc. I can use CURSOR or WHILE开发者_Go百科 for this but, I read that CURSOR is not recommended and that WHILE is slower than CURSOR. Is there another way or in this cases CURSOR\WHILE is the solution?

Thank you

CURSOR / WHILE is fine in this instance - there isn't a better way to call a sproc per row. If the performance of this is likely to have an impact on the system, though, be careful when you run it.

There is a better alternative if you can code it up - and that's to perform all the "copying" for the records in TableA and below in a bunch of SQL statements, avoiding cursors altogether. To summarise this suggestion - set-based rather than row-based.

Unless there's a significant advantage in having this copying performed in the stored proc, you'd be better off re-writing the copying inline in your current script.

If you're wondering how this might be achieved, if you're having to maintain foreign keys, work with identity columns, etc, you might check out my answer to How Can I avoid using a cursor....