Sql Server Stored procedure exicution taken long time
In addition to Kapil's answer, I'd like to add the following...

SQL Server is optimized to perform set-based operations.  Your stored procedure reads very much like an iterative process.  (Do this step, increment counter, do another step, etc.)  I believe that if you restructure your stored procedure to perform one operation on 1,000 rows as opposed to 1,000 operations on a single row, that you will get better performance.  One thing that might help is to understand what a Tally table is.  Use of a Tally table should be able to help you with your incrementing ID values.  You should be able to find many blogs that tell you how to set up and use a Tally table.  (Here is one example.)

Each single-record insert or update is a command that needs to be analyzed, executed and whose results need to be handled.  By reducing the number of executions by a factor of roughly 1,000, you should see improved results.  However we do not have your server, nor your data.  So you'll need to do that footwork.  From reading the two comments that you posted so far, I get the feeling that you're looking for one of us to wave a magic wand and have everything fixed, nice and easy.  Performance tuning is rarely nice and easy.

Good luck.