SQL insert multiple rows into a table using a variable with multiple values as one of the values to insert

Question

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.

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

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]

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:

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

M.Ali · Accepted Answer · 2014-02-14 17:55:10Z

4

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.

edited Feb 14, 2014 at 17:55

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

answered Feb 14, 2014 at 17:52

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL insert multiple rows into a table using a variable with multiple values as one of the values to insert

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related