repeating rows for different years

• Mark Green

  SSCrazy

  Points: 2085

  More actions

  July 3, 2006 at 8:34 am

  #112841

   

  I already have a table in the form:

  1 Key_m int 4 0

  0 GeographicCode varchar 10 1

  0 Year_census_estimate int 4 1

  0 Sex int 4 1

  0 Age_start int 4 1

  0 Age_end int 4 1

  0 Person_count float 8 1

  0 Age_range varchar 21 1

  At the moment the Year_census_estimate only has entries for 2006.  I would like to duplicate all the 2006 entries to the year 2005.  I would like to do the same for 2004 and all the way down to 1981.  I can't duplicate the key as this should be an IDENTITY column.  And obviously the corresponding year needs to be entered into Year_census_estimate.  Please could someone suggest the best, most compact and error free way of doing this. 

   

   

• Luke L

  SSC-Dedicated

  Points: 35545

  More actions

  July 3, 2006 at 9:10 am

  #647243

  Is this what you're looking for???  Please excuse the formatting....

  CREATE TABLE #myTable(

  Key_m int IDENTITY NOT NULL,

  GeographicCode varchar(10) NULL,

  Year_census_estimate int NULL,

  Sex int  NULL,

  Age_start int NULL,

  Age_end int NULL,

  Person_count float(8) NULL,

  Age_range varchar(21) NULL,

  )

  --Just some demo data here....

  INSERT INTO #myTable  (Geographiccode, Year_census_estimate, Sex, Age_Start, Age_End, Person_count, Age_range)

  VALUES ('northeast',2006,1,19,25,15000,'70')

  INSERT INTO #myTable  (Geographiccode, Year_census_estimate, Sex, Age_Start, Age_End, Person_count, Age_range)

  VALUES ('southeast', 2006, 1, 19, 25, 15000, '70')

  INSERT INTO #myTable  (Geographiccode, Year_census_estimate, Sex, Age_Start, Age_End, Person_count, Age_range)

  VALUES ('MidWest', 2006, 1, 19, 25, 15000, '70')

  INSERT INTO #myTable  (Geographiccode, Year_census_estimate, Sex, Age_Start, Age_End, Person_count, Age_range)

  VALUES ('northwest', 2006, 1, 19, 25, 15000, '70')

  INSERT INTO #myTable  (Geographiccode, Year_census_estimate, Sex, Age_Start, Age_End, Person_count, Age_range)

  VALUES ('southWest', 2006, 1, 19, 25, 15000, '70')

  --what you're really looking for.   Since you just want to repeat the data changing the year...  You could theoretically do this in a while loop changing the Year as needed.

  INSERT INTO #MyTable (Geographiccode, Year_census_estimate, Sex, Age_Start, Age_End, Person_count, Age_range)

   SELECT Geographiccode, 2005, Sex, Age_Start, Age_End, Person_count, Age_range

   FROM #MyTable

   WHERE Year_census_estimate = 2006

  SELEcT * FROM #Mytable

  -- Cleanup

  DROP TABLE #myTable

  To help us help you read this[/url]For better help with performance problems please read this[/url]

• Mark Green

  SSCrazy

  Points: 2085

  More actions

  July 3, 2006 at 9:16 am

  #647244

  Thanks,  I was about to post an update along those lines.  I think that I won't bother with the while loop.

• RyanRandall

  SSChampion

  Points: 13623

  More actions

  July 3, 2006 at 9:45 am

  #647253

  A simple tally table and a cross join will save you a while loop / repetitive coding / typing. Here's one quick implementation as an example...

  SELECT Geographiccode, Year, Sex, Age_Start, Age_End, Person_count, Age_range

   FROM #MyTable cross join (

      select distinct number + 1981 as Year from master.dbo.spt_values where number between 0 and 24) a

   WHERE Year_census_estimate = 2006

  Ryan Randall

  Solutions are easy. Understanding the problem, now, that's the hard part.