CODEWITHSUNDEEP
sqlsql-server-2008
FredTheDog
FredTheDog

Reputation: 63

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.

Upvotes: 1

Views: 1250

Answers (1)

neer
neer

Reputation: 4082

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

Upvotes: 1

Related Questions