A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Wednesday, February 20, 2008

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

No comments: