如何插入多行 - 需要一个循环?

问题描述

我有以下声明:

insert into forecast_entry.user_role_xref
        ( user_master_id ,
          role_id ,
          created_date ,
          created_by
        )
values
        ( 276 , -- user_master_id - int
          101 , -- role_id - int
          getdate() , -- created_date - datetime
          'MICHAELSK'  -- created_by - varchar(20)
        )

我需要为 role_id 101-355 生成一行(所以与上面的语句相同，除了随着 role_id 递增而重复).什么是最好的方法来做到这一点?为了完成工作，我打算编写一个具有循环的快速 C# 应用程序，但我确信这不是最好的方法，并希望在这里学习一些东西以避免将来不得不这样做(因为我我相信这种场景很常见).

I need to generate a row for role_id 101-355 (so the same statement above, except repeated with the role_id incrementing). What would be the best way to do this? To get the job done I'm intending on writing a quick C# application that will have a loop but I'm sure this isn't the best way and hope to learn something here to avoid having to do that in future (as I'm sure this kind of scenario is common).

推荐答案

你应该使用 数字表，如果你没有，你可以像这样使用 master..spt_values :

You should make use of numbers table and if you don't have one you can use master..spt_values like this:

insert into forecast_entry.user_role_xref
        ( user_master_id ,
          role_id ,
          created_date ,
          created_by
        )
select 276, -- user_master_id - int
       number, -- role_id - int
       getdate() , -- created_date - datetime
       'MICHAELSK'  -- created_by - varchar(20)
from master..spt_values
where type = 'P' and
      number between 101 and 355