Is it wiser to use a function in between First and Next Insertions based on Select?

Question

 PROCEDURE add_values 
 AS BEGIN
    INSERT INTO TableA 
       SELECT id, name 
       FROM TableC ("This selection will return multiple records")

While it inserts in TableA i would like insert into another table(TableB) for that particular record which got inserted in tableA

Note:The columns in TableA and TableB are different , is it wise to call a function before inserting into TableB as i would like to perform certain gets and sets based on the id inserted in tableA.

Answer 1

If you want to insert a set of rows into two tables, you'd have to store it in a temporary table first and then do the two INSERT statement from there

INSERT INTO #TempTable
   SELECT id, name 
   FROM TableC ("This selection will return multiple records")

INSERT INTO TableA
   SELECT (fieldlist) FROM #TempTable

INSERT INTO TableB
   SELECT (fieldlist) FROM #TempTable

Answer 2

Apart from Marc_S answer, one more way is

First insert the needed records into Table A from Table C. Then pump the needed records from Table A to Table B

Though many ways has been suggested by many peoples in your question that u asked just 3 hrs ago How to Insert Records based on the Previous Insert