SQL Server 2008 使用 WHILE LOOP 插入 [英] SQL Server 2008 Insert with WHILE LOOP

问题描述

我有像

ID    Hospital ID     Email                Description 
1       15         [e-mail]           Sample Description
2       15         [e-mail]          Random Text

在这种情况下，我需要使用 WHILE 循环插入医院 ID 更改为特定值或 32 的行，而其他行(不是自动生成的 ID)保持不变.

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.

它应该看起来像

ID    Hospital ID     Email                Description 
1       15         [e-mail]           Sample Description
2       15         [e-mail]          Random Text
3       32         [e-mail]           Sample Description
4       32         [e-mail]          Random Text

注意上面现在有两个新行，ID 和医院 ID 不同.ID 是自动生成的.

Notice the above now has two new rows with ID and Hospital ID different. ID is auto generated.

我有几个表需要进行相同的更新.如果我可以用 while 循环来做到这一点，我不想使用游标.

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 循环作为更简单的解决方案.

EDIT Abandoned while loop as a simpler solution was provided in the accepted answer.

推荐答案

假设 ID 是一个标识列:

Assuming that ID is an identity column:

INSERT INTO TheTable(HospitalID, Email, Description)
SELECT 32, Email, Description FROM TheTable
WHERE HospitalID <> 32

尽量避免 SQL 循环.试着从集合的角度思考.

Try to avoid loops with SQL. Try to think in terms of sets instead.

这篇关于SQL Server 2008 使用 WHILE LOOP 插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！