Q:

Insert into Table using multiple columns

Assume that we have a table that looks like this:

Comma1 Column is a personID value, and comma2, comma3, comma4 etc (this table is created dynamically so it can be more than 4 columns) are teamIDs.
Now, I have another table called personTeam that keeps relations between personids and teamids. For example if person 13 is a member of team 3 and team 8 then in personTeam table should be 2 records (13,3 and 13,8).
What i need is, using this table in the attached picture, to insert personids and teams to the PersonTeam table. For example for person 149 I want an insert (149,10) but for person 221 i want (221,7) ,(221,8), (221,11). 
Is there any way to work around this?
Thank you

A:

One way to pull this off is using UNION or UNION ALL
For example:
insert into personTeam (personid, teamid)
select comma1 as personid, comma2 as teamid
from commastable
where comma2 is not null
union
select comma1, comma3
from commastable
where comma3 is not null
union
select comma1, comma4
from commastable
where comma4 is not null;

A UNION will only return unique records, but to do that it has to sort the result.  Which can make it slightly slower than a UNION ALL which would just stick the results of the unioned queries together.
I expect that the source data wouldn't contain duplicates, so the UNION is just used to make sure it only inserts unique tupples. 
And if you want to make this repeatable?
Just put that query in a sub-query and left join it to personTeam .
insert into personTeam (personid, teamid)
select q.personid, q.teamid
from
(
  -- add the query with the unions here
) as q
left join personTeam as t 
  on (t.personid = q.personid and t.teamid = q.teamid)
where t.personid is null;

