Finally found a cool use of a SQL Cross Join

Setup:
Lets say you have a list of contacts stored in a contacts table and a list of attributes stored in an attributes table.  So lets say that each contact can have one or more attributes and each attribute can be used on one or more contacts.  To do this we would need to use a many to many table, something that stores the id of the contact and the id of the attribute.  For example:

create table ContactsAttributes (
    ContactID int not null,
    AttributeID int not null
)

Now lets say we just added 2 new attributes (attribute id: 10 and 11) to the attribute table and we needed to add them to 3 specific contacts (contact id: 1,2,3).  In this instance it would be easier to just write out the insert statements like so:

insert ContactsAttributes (ContactID, AttributeID)
values
(1, 10),
(1, 11),
(2, 10),
(2, 11),
(3, 10),
(3, 11)

As you can see this is pretty straight forward.  Now lets say we added 10 attributes and we needed to add all 10 of these attributes to 20 specific contacts.  Since there are 10 attributes that need to be added to 20 contacts that would be 200 items to write in the table constructor of the insert statement.  Using a Cross Join like below would be considerably less code and way cooler.

DECLARE @contacts AS TABLE (id int)
DECLARE @attributes AS TABLE (id INT)

INSERT @contacts (id) VALUES
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)
,(12),(13),(14),(15),(16),(17),(18),(19),(20)

INSERT @attributes ( id ) VALUES
(10),(11),(12),(13),(14),(15)
,(16),(17),(18),(19),(20)

INSERT INTO ContactsAttributes
SELECT c.id, a.id
FROM @contacts c
CROSS JOIN @attributes a

Using the cross join basically returns all possible combinations of the id column in the contacts table variable with the id column of the attributes table variable.  This is what is known as a Cartesian Product.  To see the results as they would be inserted comment out or remove the “INSERT INTO ContactsAttributes” line.