Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2005
    Posts
    30

    Unanswered: Create table with 365 rows

    Hi, I need to create a table dynamically with 365 rows in it (using a stored procedure). I think in SQL Server 2008 there is a way of doing this quite easily but I am using 2005.
    I am thinking that a loop inside the body of the sproc would work but am not sure how to do it. Could somebody give me some pointers?
    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm not quite following what yoiu want... When you create any table, it has no rows. Then you add rows as needed.

    As to the rows, you might want to consider 366 rows depending on how you intend to use them.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2005
    Posts
    30
    Quote Originally Posted by Pat Phelan
    I'm not quite following what yoiu want... When you create any table, it has no rows. Then you add rows as needed.

    As to the rows, you might want to consider 366 rows depending on how you intend to use them.

    -PatP

    Sorry, I meant columns, I want to have a column for every day of the year e.g.

    CREATE TABLE Year2(ID1 int, ID2 int)

    Except I want 'ID1', 'ID2', to be created automatically by a loop all the way up to 'ID365'.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Nope!

    Dynamically creating tables - nope.
    doing this in a sproc - nope.
    365 columns - nope.

    You want a permanent numbers/calendar table!

    numbers:
    Code:
    CREATE TABLE dbo.numbers (
       number int NOT NULL
     , CONSTRAINT pk_numbers
         PRIMARY KEY CLUSTERED (number)
         WITH FILLFACTOR = 100
    )
    
    INSERT INTO dbo.numbers (number)
    SELECT (a.number * 256) + b.number As number
    FROM   (
            SELECT number
            FROM   master..spt_values
            WHERE  type = 'P'
            AND     number <= 255
    	) As a
     CROSS
      JOIN (
            SELECT number
            FROM   master..spt_values
            WHERE  type = 'P'
            AND     number <= 255
    	) As b
    and a few twaeks later - calendar:
    Code:
    CREATE TABLE dbo.calendar (
       the_date datetime NOT NULL
     , CONSTRAINT pk_calendar
         PRIMARY KEY CLUSTERED (the_date)
         WITH FILLFACTOR = 100
    )
    
    INSERT INTO dbo.calendar (the_date)
    SELECT DateAdd(dd, (a.number * 256) + b.number, 0) As the_date
    FROM   (
            SELECT number
            FROM   master..spt_values
            WHERE  type = 'P'
            AND     number <= 255
    	) As a
     CROSS
      JOIN (
            SELECT number
            FROM   master..spt_values
            WHERE  type = 'P'
            AND     number <= 255
    	) As b
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'll grant you that I've only been working on databases for a bit over 30 years, but so far I've never heard a reasonable explanation of why you'd want anywhere near 365 columns in a table... For reports and queries I've actually had a couple of reasonable explanations, but even when I fogured out how to deliver those the user changed their request.

    Would you care to take a shot at explaining how you plan to use a table with that many columns? Maybe if I understand that, I can help you get to a more managable design. It is possible to create a table with that many columns, but it takes some planning to make it work and I've not yet seen any problem that required it.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Feb 2005
    Posts
    30
    Apologies Pat, I was a bit pressed fro time when I posted yesterday. I am creating a hotel bookings application and I need a way of keeping track of all rooms reserved and the dates of the reservations.
    I was going to have just one table with a field for every year (365) but when there is a leap year (2012) I will have a problem.
    So now I am thinking that I will need two tables (Rooms and Reservations ).
    Rooms will have two fields (roomnumber (INT) and type (VARCHAR))
    Reservations will have two fields roomnumber (INT) and reserved (DATE).
    I should be able to query the two at the same time with a start date and an end date and get a positive or negative answer as to whether the dates submitted are free.

  7. #7
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    I suggest you have both a start and an end date in your Reservations table. I assume there will be more to your design than you've described. For example a way of creating a single booking with multiple reservations and relating bookings to customers and costs.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •