generating records from a single record

• sqlstar2011

  SSCertifiable

  Points: 6000

  More actions

  January 27, 2009 at 9:50 am

  #128269

  So I have two tables with similar structures

  CREATE TABLE [dbo].[ABC](

  [COLL] [char](6) NOT NULL,

  [FYear] [char](4) NOT NULL

  )

  CREATE TABLE [dbo].[XYZ](

  [COLL] [char](6) NOT NULL,

  [FY_Period] [char](4) NOT NULL

  )

  and I insert two rows into ABC tables

  insert into abc values ('123456','2009')

  insert into abc values ('123457','2009')

  what I want to accomplish is create 12 rows for each row in ABC table.

  for example the first record in ABC is Coll= 123456 and Fyear=2009

  for this record(and each record in ABC table) I would like to insert following entries into XYZ

  123456, 200901

  123456, 200902

  123456, 200903

  123456, 200904

  123456, 200905

  123456, 200906

  123456, 200907

  123456, 200908

  123456, 200909

  123456, 200910

  123456, 200911

  123456, 200912

  basically I am keeping the value for COLL column same but generating twelve values for 2009. one of each month.

  what can be the best way to accomplish this?

• noeld

  SSC Guru

  Points: 96590

  More actions

  January 27, 2009 at 11:03 am

  #933390

  select coll, FYear + months.m

  from ABC

  cross join ( select '01' as m union all

  select '02' union all

  select '03' union all

  select '04' union all

  select '05' union all

  select '06' union all

  select '07' union all

  select '08' union all

  select '09' union all

  select '10' union all

  select '11' union all

  select '12' ) months

  
  * Noel

• sqlstar2011

  SSCertifiable

  Points: 6000

  More actions

  January 27, 2009 at 1:33 pm

  #933479

  works like a charm.. thanks