FredTheDog FredTheDog - 3 months ago 9
SQL Question

INSERT multiple rows based on SELECT statement

I am using SQL Server 2008 and with the help of other threads I have been able to write the following:

insert into fml0grant (auto_key, roleid)
select fml0.auto_key, 20
from fml0
left join fml0grant on fml0.auto_key = fml0grant.auto_key
where fml0.dwgname <> ''
and fml0grant.roleid is null


However what I need to do is insert multiple rows for each record found in the where clause. So when the where clause gets a result I need to insert:


  1. fml0.auto_key, 20

  2. fml0.auto_key, 508

  3. fml0.auto_key, 10



Is there any way to combine all three inserts into one statement as after the first in my query the NULL in the WHERE clause is no longer true.

Answer

You can use CROSS JOIN as the below.

insert into fml0grant (auto_key, roleid)
    select fml0.auto_key, V.Id 
    from fml0
    left join fml0grant on fml0.auto_key = fml0grant.auto_key
    CROSS JOIN (VALUES (20),(508),(10)) V (Id)
    where fml0.dwgname <> ''
      and fml0grant.roleid is null