Dfyjkt

I have the following SQL Server 2016 table that holds all possible combination of 3 colors and 3 shapes:

Color Shape
---------------------
red square
red circle
red octagon
yellow square
yellow circle
yellow octagon
green square
green circle
green octagon 

I have the following data table that contains "objects" as follows (there are no duplicates):

Object ID Shape Color
------------------------------------------------
object1 square green
object1 square red
object1 octagon yellow
object1 circle green
object2 circle red
object2 square yellow
object3 square red
object3 circle red
object3 square yellow
object3 square yellow
object3 octagon green
object4 circle red
etc......
etc......
object100 

As you can see there are some "gaps" in terms of shape+color combination for each object.

What I would like to achieve is to insert any missing combination of shape+color for each [object] record. Desired output for object1 and object2 would be for example:

 Object ID Color Shape
 -------------------------------------------
 object1 red square
 object1 red circle
 object1 red octagon
 object1 yellow square
 object1 yellow circle
 object1 yellow octagon
 object1 green square
 object1 green circle
 object1 green octagon 
 object2 red square
 object2 red circle
 object2 red octagon
 object2 yellow square
 object2 yellow circle
 object2 yellow octagon
 object2 green square
 object2 green circle
 object2 green octagon
 etc......

Thank you

I think you can use this to insert your missing values in this way based on the output you have mentioned as well.

create table color (colors varchar(10), Shape varchar(10))
insert into color values 
('red' , 'square' )
,('red' , 'circle' )
,('red' , 'octagon' )
,('yellow' , 'square' )
,('yellow' , 'circle' )
,('yellow' , 'octagon' )
,('green' , 'square' )
,('green' , 'circle' )
,('green' , 'octagon' ) 


create table objectsnew (objectID varchar(20), Shape varchar(10),colors varchar(10))
insert into objectsnew values
 ('object1' , 'square' , 'green' )
,('object1' , 'square' , 'red' )
,('object1' , 'octagon' , 'yellow' )
,('object1' , 'circle' , 'green' )
,('object2' , 'circle' , 'red' )
,('object2' , 'square' , 'yellow' )
,('object3' , 'square' , 'red' )
,('object3' , 'circle' , 'red' )
,('object3' , 'square' , 'yellow' )
,('object3' , 'square' , 'yellow' )
,('object3' , 'octagon' , 'green' )
,('object4' , 'circle' , 'red' )

--Use this to make sure that you are inserting only unique combination for each object ID.

insert into objectsnew (objectID, Shape, colors) 

select o.objectID, c.Shape, o.colors from color c 
join objectsnew o on c.colors = o.colors 
except 
select objectID, Shape, colors from objectsnew 
order by o.objectID , o.colors

Output

 objectID Shape colors
 object1 octagon green
 object1 circle green
 object1 square green
 object1 square red
 object1 octagon red
 object1 circle red
 object1 circle yellow
 object1 square yellow
 object1 octagon yellow
 Etc.....

You can try to use CROSS JOIN and DISTINCT

SELECT t2.[Object ID],t1.*
FROM 
 (SELECT DISTINCT Color,Shape FROM T1) t1
CROSS JOIN
 (SELECT DISTINCT [Object ID] FROM T2) t2

sqlfiddle

The following generates the rows to be inserted:

select cs.color, cs.shape, oi.objectID
from colorshape cs cross join
 (select distinct o.objectID from objects o) oi
where not exists (select 1
 from objects o
 where o.color = cs.color and
 o.shape = cs.shape and
 o.objectId = oi.objectId
 );

You can add an insert before this to insert these into the table.