Recursive CTE

I ran into a situation today where we needed some dummy data loaded into a brand new table to make a rough prototype.  While I could have done lots of typing to get in there, I thought this was a decent opportunity to use a recursive common table expression (CTE).

CREATE TABLE 
    DBO.COMPANY_DEPARTMENT
(
    company_id int
,   company_name varchar(255)
,   department_id int
,   department_name varchar(255)
,   PRIMARY KEY CLUSTERED (company_id, department_id)
)
GO

;
-- load up some dummy data
WITH COMPANY AS
(
   SELECT
       0 AS company_id
   ,   'Sample company' AS company_name
)
, DEPARTMENT AS
(
   SELECT
       20 AS department_id
   ,   'Department ' + char(65 + 20) AS department_name
   UNION ALL
   SELECT
       department_id -1
   ,   'Department ' + char(65 + department_id -1) AS department_name
   FROM
       DEPARTMENT
   WHERE
       department_id > 0
)
INSERT INTO
   COMPANY_DEPARTMENT
SELECT
   *
FROM
   COMPANY
   CROSS APPLY
       DEPARTMENT

The above query will kick out data like
0,Sample Company, 0, Department A
0,Sample Company, 1, Department B

from 0 to 20