Cross Join on a subset of 2 tables
<p>I'm attempting to copy data from an old database design to a new database design.</p>

<p>The database is for a multi-tenancy application, and the source table in question is a table of widget category names.</p>

<p>Each tenant has their own private set of category names, so the old table looks like this:</p>

<pre><code>CREATE TABLE OldCategories (
    TenantId     bigint,
    CategoryName nvarchar(255)
)

OldCategories:
TenantId, CategoryName
       1, 'Dog Toys'
       1, 'Cat Toys'
       1, 'Lizard Toys',
       2, 'Dog Toys'
       2, 'Duck Toys'
</code></pre>

<p>The new database adds an intermediate table such that all of a tenant's categories must be duplicated for each row in the intermediate table, here is the new schema:</p>

<pre><code>CREATE TABLE NewCategoryGroups (
    TenantId  bigint,
    GroupId   bigint IDENTITY,
    GroupName nvarchar(255)
)

CREATE TABLE NewCategories (
    GroupId      bigint,
    CategoryName nvarchar(255)
    CONSTRAINT PRIMARY KEY ( GroupId, CategoryName )
)
</code></pre>

<p>Here's what's in the NewCategoryGroups table:</p>

<pre><code>NewCategoryGroups:
TenantId, GroupId, GroupName
       1,       1, '2006 Toys'
       1,       2, '2010 Toys'
       1,       3, '2016 Toys'
       2,       4, '2007 Toys'
       2,       5, '2010 Toys'
</code></pre>

<p>And here is the example data I want to get for the NewCategories table:</p>

<pre><code>NewCategories 
GroupId, CategoryName
      1, 'Dog Toys' -- Groups 1 through 3 are for Tenant 1
      1, 'Cat Toys'
      1, 'Lizard Toys',
      2, 'Dog Toys'
      2, 'Cat Toys'
      2, 'Lizard Toys',
      3, 'Dog Toys'
      3, 'Cat Toys'
      3, 'Lizard Toys',
      4, 'Dog Toys' -- Groups 4 and 5 are for Tenant 2
      4, 'Duck Toys'
      5, 'Dog Toys'
      5, 'Duck Toys'
</code></pre>

<p>I've tried a <code>CROSS JOIN</code>, as-in:</p>

<pre><code>SELECT
    CategoryName
FROM
    OldCategories
    CROSS JOIN NewCategoryGroups
</code></pre>

<p>...except this loses the TenantId distinction, so every tenant will get every other tenant's categories.</p>