SQL Server 2008 使用 WHILE LOOP 插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15182468/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server 2008 Insert with WHILE LOOP
提问by Mukus
I have existing records like
我有现有的记录，如
ID Hospital ID Email Description
1 15 [email protected] Sample Description
2 15 [email protected] Random Text
I need to use a WHILE loop to insert rows with Hospital ID changing to a specific value or 32 in this case, while the others(not ID as it is auto generated) remaining constant.
在这种情况下，我需要使用 WHILE 循环插入医院 ID 更改为特定值或 32 的行，而其他行（不是自动生成的 ID）保持不变。
It should then look like
它应该看起来像
ID Hospital ID Email Description
1 15 [email protected] Sample Description
2 15 [email protected] Random Text
3 32 [email protected] Sample Description
4 32 [email protected] Random Text
Notice the above now has two new rows with ID and Hospital ID different. ID is auto generated.
请注意，上面现在有两个新行，ID 和医院 ID 不同。ID是自动生成的。
I have several tables where I need to make the same updates. I don't want to use cursor if I can do this with a while loop.
我有几个表需要进行相同的更新。如果我可以用 while 循环来做到这一点，我不想使用游标。
EDITAbandoned while loop as a simpler solution was provided in the accepted answer.
EDITAbandoned while loop 作为更简单的解决方案在接受的答案中提供。
回答by peterm
First of all I'd like to say that I 100% agree with John Saunders that you must avoid loops in SQL in most cases especially in production.
首先，我想说我 100% 同意 John Saunders 的观点，即在大多数情况下，尤其是在生产中，您必须避免 SQL 中的循环。
But occasionally as a one time thing to populate a table with a hundred records for testing purposes IMHO it's just OK to indulge yourself to use a loop.
但偶尔作为一次性的事情来填充一个带有一百条记录的表以进行测试，恕我直言，放纵自己使用循环是可以的。
For example in your case to populate your table with records with hospital ids between 16 and 100 and make emails and descriptions distinct you could've used
例如，在您的情况下，使用医院 ID 介于 16 到 100 之间的记录填充您的表格，并使电子邮件和描述与众不同，您可以使用
CREATE PROCEDURE populateHospitals
AS
DECLARE @hid INT;
SET @hid=16;
WHILE @hid < 100
BEGIN
INSERT hospitals ([Hospital ID], Email, Description)
VALUES(@hid, 'user' + LTRIM(STR(@hid)) + '@mail.com', 'Sample Description' + LTRIM(STR(@hid)));
SET @hid = @hid + 1;
END
And result would be
结果将是
ID Hospital ID Email Description
---- ----------- ---------------- ---------------------
1 16 [email protected] Sample Description16
2 17 [email protected] Sample Description17
...
84 99 [email protected] Sample Description99
回答by John Saunders
Assuming that IDis an identity column:
假设这ID是一个标识列：
INSERT INTO TheTable(HospitalID, Email, Description)
SELECT 32, Email, Description FROM TheTable
WHERE HospitalID <> 32
Try to avoid loops with SQL. Try to think in terms of sets instead.
尽量避免 SQL 循环。试着从集合的角度思考。
