INSERT multiple rows based on SELECT statement

Question

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:

fml0.auto_key, 20
fml0.auto_key, 508
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.

This question is not clear to me,please look here on how to improve question.. — TheGameiswar
– TheGameiswar, Commented Aug 18, 2016 at 10:51

neer · Accepted Answer · 2016-08-18 10:59:52Z

1

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

answered Aug 18, 2016 at 10:59

neer

4,0926 gold badges22 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

FredTheDog Over a year ago

Works a treat, Thanks NEER

Collectives™ on Stack Overflow

INSERT multiple rows based on SELECT statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related