在MS SQL存储过程中是否可以使用循环语句? [英] Is there any option to use loop statement in MS SQL Stored Procedure?

问题描述

在MS SQL存储过程中是否可以使用循环语句?
假设我有两个表Table-A和Table-B
表格A
身份证名称电话
1 X 12354
2 Y 3689
3 Z 2578

我想使用存储过程将表A的所有值插入表B
我正在使用MS SQL 2005

在此先感谢
Rashed

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

推荐答案

在MS SQL中，要进行循环，可以使用WHILE循环以及游标.
但是还有其他方法可以解决您的问题.
下面提到其中一些:
1.您可以使用select执行插入.
例如.插入到TableB中([ID]，[名称]，[电话])
从TableA中选择[ID]，[名称]，[电话]

2.如果TableB不存在，则可以执行select-into语句.
例如.从表A SELECT * INTO TableB

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

如上所述，可能发生WHILE循环

As mentioned, a WHILE loop is possible

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

但是，对于您想做的事情，这完全是错误的事情. 旋风"答案是正确的.

您绝对需要在SQL的这些方面进行更多思考，摆脱循环的想法-您通常可以通过正确使用SQL来实现所需的功能.

在少数情况下，我会在TSQL中选择一个循环.

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.

阅读有关表值函数的信息，对您有帮助.那将是一个更好的方法.

但是，关于循环问题，您问-游标是提供循环功能的一种.

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.

这篇关于在MS SQL存储过程中是否可以使用循环语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！