SQL使用具有多个值的变量作为要插入的值之一，将多个行插入到表中

I want to

我想

INSERT INTO TABLE (col1, col2, col3) [col1 through col3 are non null columns]
VALUES (val1,val2,val3)

All vals are uniqueidentifiers. What I am doing is updating a table that has a unique member id for val1 and a unique ribbon key for val2 and a unique created by key for val3. What this does on the UI side is assigns a ribbon to a member and has to be created by a member. What I want to do is assign val2 and val3 to 100 members. So i wanted to know if I could do a select query that returns the list of 100 member ids and assign that list of ids to a variable which i would use for val1.

所有vals uniqueidentifiers。我正在做的是更新一个表，该表有一个惟一的val1成员id，以及val2的唯一的ribbon键，以及val3的键所创建的唯一键。它在UI端所做的是为成员分配一条丝带，并且必须由成员创建。我要做的是将val2和val3分配给100个成员。所以我想知道我是否可以做一个select查询返回100个成员id的列表并将这个id列表分配给一个变量我将用于val1。

when trying to get the variable assigned with all the ids using this:

当尝试使用以下方法获得所有id分配的变量时:

declare @members uniqueidentifier
set @members  = (
Select Members.MemberID from Members
where departmentkey = 'D22CF614-721B-4C15-ABF6-02541CDC7502')

[this returns all the members from a particular department of 100 members]

[这将从100名成员的特定部门返回所有成员]

i get this error:

我得到这个错误:

Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

So my first step is to find out how to have this variable @members have assigned the list of member ids. Then second step is to assign all those members the ribbon like this:

因此，我的第一步是找出如何让这个变量@members分配了成员id列表。第二步是像这样给所有成员分配丝带:

INSERT INTO TABLE (col1, col2, col3) 
VALUES (@members,val2,val3)

Is there a way to do what I am trying to do? Let me know if you need more information. Thanks

有办法做我想做的事吗?如果你需要更多的信息，请告诉我。谢谢

1 个解决方案

#1

Try doing this:

试着这样做:

INSERT INTO TABLE (col1, col2, col3) 
    Select Members.MemberID, val2,val3
    from Members
    where departmentkey = 'D22CF614-721B-4C15-ABF6-02541CDC7502';

Don't bother with the variable.

不用麻烦变量了。

#1