取决于你如何运行查询，但我会使用CTE创建一个4,5,6,7的表：

Depends how you are running the query, but i would use a CTE to create a table of 4,5,6,7:

with num as (
 select 4 as num
 union all 
 select num +1
 from test 
 where num < 7)
Insert into S_GLOBALFIELDS_FACT(S_ID,B_ID,G_ID,AMOUNT)
select '150',nums.num,G_ID,AMOUNT 
from S_FACT, nums
where S_ID=2 and B_ID =(2)

那将为每行插入4个项目，其中B_ID = 2



如果你想在B_ID = 4,5,6或7的地方选择B_ID，那么只需使用IN运算符：

That will insert 4 items for each row where B_ID = 2

If you want the B_ID to be selected where B_ID = 4, 5, 6 or 7 then just use a "IN" operator:

Insert into S_GLOBALFIELDS_FACT(S_ID,B_ID,G_ID,AMOUNT)
select '150',nums.num,G_ID,AMOUNT 
from S_FACT, nums
where S_ID=2 and B_ID IN (4,5,6,7)

希望有所帮助^ _ ^

Andy





更新：如何将数组作为参数传递

假设：

1：您正在使用sql server 2008或更高版本。

2：您在代码中使用SqlClient Connection。



首先你必须创建用户定义的类型：

Hope that helps ^_^
Andy


UPDATE: How to pass arrays as parameters
Assumptions:
1: You're using sql server 2008 or later.
2: You're using SqlClient Connection in your code.

First you have to create a user defined type:

CREATE TYPE int_list_table AS TABLE (n int NOT NULL PRIMARY KEY)

这将存储在您的数据库中以备将来使用。您可以将其用作存储过程参数。

This will be stored in your database for future use. You can use this as a stored procedure parameter.

Create Procedure My_Insert(
@ids as int_list_table
)
AS
BEGIN

  
  Insert into S_FACT(S_ID,B_ID,G_ID,AMOUNT)
  select '150',B_ID,G_ID,AMOUNT 
  from S_FACT s
  inner join @ids i on s.B_ID = i.n
  where S_ID=2

  /* or using "IN"
  Insert into S_FACT(S_ID,B_ID,G_ID,AMOUNT)
  select '150',B_ID,G_ID,AMOUNT 
  from S_FACT 
  where S_ID=2 and  B_ID in (select n from @ids)
  /*
END

您可以将数组作为参数传递而不指定类型

You can pass the array as a parameter without specifying a type

using (conn)
{
    SqlCommand cmd = new SqlCommand("dbo.My_Insert", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter dtparam = cmd.Parameters.AddWithValue("@ids", ids);
    dtparam.SqlDbType = SqlDbType.Structured;
}

我希望有所帮助^ _ ^

Andy

I hope that helps ^_^
Andy