This is pretty much a classic
ETL[
^] task
Depending on your version of SQL Server, you should look at either creating a DTS package (legacy, SQL2000) or an SSIS package
In an SSIS package, you design the steps within Visual Studio before deploying to your SQL Server - something like you've described could be achieved very easily.
Have a look into SSIS. Do you have all the tools required to use this? If so, I'd recommend using this