Click here to Skip to main content
15,790,135 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Is there any option to use loop statement in MS SQL Stored Procedure?
Suppose I have two tables Table-A and Table-B
ID Name Phone
1 X 12354
2 Y 3689
3 Z 2578

I want to insert all the values of Table-A into Table-B by using Stored Procedure
I am using MS SQL 2005

Thanks in advance

In MS SQL, for looping, you can use WHILE loop as well as Cursors.

But there are other ways to solve your problem.
Some of them are mentioned below:
1. You can perform insert with select.
E.g. Insert into TableB ([ID], [Name], [Phone])
SELECT [ID], [Name], [Phone] FROM TableA

2. If TableB does not exists, then you can perform select-into statement.
Share this answer
As mentioned, a WHILE loop is possible

SET @ICount = 1
WHILE (@ICount <= @10)
 -- Do something!
 SET @ICount = (@ICount + 1)

However, for what you want to do it's completely the wrong thing. 'The Cyclone' answer is correct.

You definitely need to think more along these lines with SQL, get away from the idea of loops - you can usually achieve what you need with the correct usage of SQL.

There are only a few rare cases where I choose a loop in TSQL.
Share this answer
[no name] 6-Jul-10 6:08am    
Read about Table Value Function, that should help you out. That would be a better approach to do it.

Yet, about the loop thing you asked - Cursors are the one that provide loop type of functionality.
Share this answer
Cyclone mentioned all the easiest ways to insert data from one table to another.

i like to mention about 'Recursive Queries' in SQL, which is another alternate for loop.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900