sql - Find and insert dummy rows - possible scenario for OUTER APPLY -

this mock of situation we've got:

if object_id('tempdb..#people') not null begin drop table #people end; create table #people     (     name varchar(100),     category varchar(20),     id int     ); insert #people     values     ('x','bronze',1),     ('y','bronze',2),     ('z','silver',3),     ('j','gold',4),     ('q','bronze',5),     ('x','silver',1);   if object_id('tempdb..#category') not null begin drop table #category end; create table #category     (     category varchar(100)     ); insert #category     values     ('gold'),     ('silver'),     ('bronze'); 

if name not have category e.g. x not have gold i'd row creating , adding table #people id of -1.

current solution have this:

with x     (     select  distinct             x.name,             s.category        #people x             cross join #category s     ) insert     #people select  j.name,         j.category,         id = -1    x j   not exists         (         select  1            #people q           j.name = q.name                 , j.category = q.category         ); 

see works!...:

select * #people; 

i have feeling cross apply might operator use in order simplify above - simplest way find, create , insert these rows?

insert people(name, category, id) ( select distinct   p.name,    c.category,    p.id           people p    cross join category c   concat(p.id, c.category) not in (select concat(id, category) people) ); 

http://www.sqlfiddle.com/#!6/92cd5/14