Is there any option to use loop statement in MS SQL Stored Procedure?

Question

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
Table-A
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
Rashed

Posted 6-Jul-10 0:09am

Rasadul Alam Rashed

Add a Solution

4 solutions

Solution 3

As mentioned, a WHILE loop is possible

SQL

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

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.

Posted 6-Jul-10 1:07am

Dylan Morley

Comments

[no name] 6-Jul-10 6:08am

Thanks...

Solution 1

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.

Posted 6-Jul-10 0:19am

Sandeep Mewara

Solution 4

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.

Posted 25-May-11 0:48am

Liju Sankar

Add a Solution

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

The Cyclone · Accepted Answer · 2010-07-06T00:43:00

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.
E.g. SELECT * INTO TableB FROM TableA

Is there any option to use loop statement in MS SQL Stored Procedure?

4 solutions

Solution 2

Solution 3

Solution 1

Solution 4

Add your solution here

Preview 0

Is there any option to use loop statement in MS SQL Stored Procedure?

4 solutions

Solution 2

Solution 3

Solution 1

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us