Stored procedure to insert into table each night

You should consider putting the TRUNCATE and INSERT into a single transaction and checking for errors during the INSERT, so that if the INSERT goes wrong at least you can roll back the truncate and the table won't be empty.

I've got that working but what I need to do once that finishes is actually take that table and do a select from it to create another table because the data can't be manipulated properly until it is all combined.

Once this is done inside the same stored procedure can I have it take the result and actually populate another table (truncate that as well) so the whole thing is done in one sp or do I have to create two stored procedures to run one after the other so once one finishes then run the other?

This needs to be in the second part of the stored procedure.
            select * from
      (select [LastName],[FirstName],[Sex],[Language],[Ethnicity],[Race],[DOB],[ResultDescription],[A1cResultValue],[A1cDate],[enc_id],[person_id],
      ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY A1cDate DESC)rowno2
      from A1c_PIP_Q1_2016 )A1c
      where rowno2 = 1

Scott that is a good point. I'm not sure how to do that and actually that plays into what I just mentioned above in that I need to do this job/stored procedure in stages so once one part completes it moves on to the next part.

>  ... but what I need to do once that finishes is ...
@biotec - Please do us a favor and include all of your requirements in the original question, and not in a stream of follow-on comments, so that we can get you answers faster without multiple iterations of comments, and running the risk of losing experts between comments.    ​Top 10 Ways to Ask Better Questions, Number Ten.    Thanks in advance.

Sorry for the additional comments guys. I figured out how to do what I needed thanks to your comments. Appreciate it.