CODEWITHSUNDEEP
sqlsql-server
Nathan Pena
Nathan Pena

Reputation: 317

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

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

Upvotes: 2

Views: 954

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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.

Upvotes: 4

Related Questions