CROSS JOIN Example : Multiplying data rows

This example uses a CROSS JOIN to multiply data rows to a large result set.
The result will contain every combination of accountcode, month and year hence this technique is good for generating dimension tables.

-- create 3 temporary tables for the purpose of this demonstration

if object_id('tempdb..#accountcodes') is not null
 begin
 drop table #accountcodes
 end

create table #accountcodes (companycode char(10))

 insert into #accountcodes (companycode) values ('008')
 insert into #accountcodes (companycode) values ('009')
 insert into #accountcodes (companycode) values ('010')
 insert into #accountcodes (companycode) values ('011')


if object_id('tempdb..#years') is not null
 begin
 drop table #years
 end

create table #years (yearvalue int)

 insert into #years (yearvalue) values (2008)
 insert into #years (yearvalue) values (2007)
 insert into #years (yearvalue) values (2006)
 insert into #years (yearvalue) values (2005)
 insert into #years (yearvalue) values (2004)
 insert into #years (yearvalue) values (2003)

if object_id('tempdb..#months') is not null
 begin
 drop table #months
 end

create table #months (monthvalue int)

 insert into #months (monthvalue) values (1)
 insert into #months (monthvalue) values (2)
 insert into #months (monthvalue) values (3)
 insert into #months (monthvalue) values (4)
 insert into #months (monthvalue) values (5)
 insert into #months (monthvalue) values (6)
 insert into #months (monthvalue) values (7)
 insert into #months (monthvalue) values (8)
 insert into #months (monthvalue) values (9)
 insert into #months (monthvalue) values (10)
 insert into #months (monthvalue) values (11)
 insert into #months (monthvalue) values (12)


-- finally, perform the cross joins to get the final results.
-- there should be (4 x 6 x 12) 288 rows returned.

select * from #accountcodes cross join #years cross join #months