Storing/inserting Multiple Values

[mikev1976]

I have two tables - Member.Users and Security.EntityReferences. In order for certain permissions to be granted, the UserId from the Member.Users table must have an entry in the EntitySecurityKey column of the Security.EntityReferences table. I know how to fix an individual person, but I found that there are many missing and was trying to fix many at once but cannot figure out how. Each user has an associated SponsorId also. I can find all the users for a particular sponsor that do not have an entry in Security.EntityReferences.I can fix an idividual like so: insert Security.EntityReferencesselect NEWID(),select UserId from Member.Users where NameFirst='abc' and NameLast='xyz' and Sponsorid=(select SponsorId from Sponsor.Sponsors where SponsorCode='somesponsor') I can find all the users for a particular sponsor with no entry in Security.EntityReferences like so: select u.UserId from Member.Users u join Sponsor.Sponsors s on u.SponsorId=s.SponsorIdwhere u.UserId not in(select EntitySecurityKey from Security.EntityReferences) and s.SponsorCode='somesponsor' I tried to insert them all at once like so: while (select COUNT(userid) from Member.users where userid in(select u.UserId from Member.Users u join Sponsor.Sponsors s on u.SponsorId=s.SponsorIdwhere u.UserId not in(select EntitySecurityKey from Security.EntityReferences) and s.SponsorCode='somesponsor'))!=0BEGINinsert Security.EntityReferencesselect NEWID(),(select u.UserId from Member.Users u join Sponsor.Sponsors s on u.SponsorId=s.SponsorIdwhere u.UserId not in(select EntitySecurityKey from Security.EntityReferences) and s.SponsorCode='somesponsor')END I am getting stuck because the subquery is not allowed to return more than one result. Also I tried to declare a variable but can't store them because there is more than one value returned. I am not sure if I am approaching this right or not. Thanks in advance for help

[mikev1976]

I have found one way to do this is to create a virtual table. declare @sponsorid uniqueidentifierselect @sponsorid='somesponsor'declare @missingusers table(id uniqueidentifier,sponsorid uniqueidentifier,securityKey uniqueidentifier) insert @missingusersselect u.UserId,@sponsorid,NEWID()from Member.Users uwhere not exists(select * from Security.EntityReferences er where er.EntitySecurityKey=u.UserId )and SponsorId=@sponsorid Then I can take the values that were inserted into that table and put them in the table that I need: insert Security.EntityReferencesselect securityKey,idfrom @missingusers