Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2009
    Posts
    19

    Unanswered: Table creation problem

    Hi there,

    I have tried using a piece of query-code that I previously used with no problems. The code creates a new table, based on a previous table and looks like this:
    -------------------------------------------------------

    Select a.*, b.obstime as PrevObsTime, log(a.SPCLOSE)-log(b.SPCLOSE) as SPreturn
    into ReturnSP
    from SP a, SP b
    where b.obstime=(select max(obstime) from SP where obstime<a.obstime)


    -------------------------------------------------------

    When I try this for top 10 or top 100, it works absolutely fine and I know that it worked in the past as well with tables as large as the SP table, which I'm basing this on. However, when I try it now, I get an error message like this:

    Msg 1101, Level 17, State 12, Line 1
    Could not allocate a new page for database 'Job2' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.


    I guess this may be due to the fact that I'm using the free version of SQL server 2005. When I try to look at the properties of my database, it says that it has reached the maximum initial size of 4096 MB, but this happens also if I try the code in other databases - then they go from initial size of e.g. 12 MB to the 4096...

    Does anyone have a clue on what I should do here to get this to work? It'd be a great help to me and I would appreciate it alot!

    Best regards,

    Martin

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I would guess you have much more data in the tables than you had previously. You are generating gigabytes of data.

    You are essentially creating a table using something like half (although possibly much more, possibly much less - it depends on the distribution of obstime) the cartesian product of SP.

    So if SP contains 100 rows, your resultant table might be around 5000 rows. The point is that sort of thing scales exponentially. These rows will also be wider than the source rows in SP.


    It might be worth explaining what you are trying to do (in English, not code) - there is likely another, better way.
    Also, what version of SQL Server are you using?

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    ...and another thing - SELECT...INTO is a dog and really should only be used for teeny weeny result sets. Best practice is to create a table and then use INSERT syntax.

  4. #4
    Join Date
    Jul 2009
    Posts
    19
    Thanks for the fast reply!

    Well, the number of rows are approximately 400.000, but they were the same in the previous tables I worked with, where I had no problems.

    What I need to do is the following:

    1) I'm working with intra-day data from the S&P500. I have a table with the Date, TimeOfDay and the ClosePrice. This is the "SP" table.
    2) Basically I need a new table where the returns of each of the 5-minute interval price observations are computed, which is why the code sets up a lagged version of the table for using as the "previous" price-observation in the computation of a return.

    That's pretty much it - so basically I just need to compute the returns, but I don't know if there's a smarter way to do this? I'm not familiar with the INSERT syntax and how it works, I'm afraid.

    The version of my SQL server 2005 (free version) is 9.00.3042.00

    Thank you very much for helping out!

    Best regards,

    Martin

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - on first reading then shouldn't your query be joining on the observation date too?

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually - I think I understand now - your obstime column includes date too?

  7. #7
    Join Date
    Jul 2009
    Posts
    19
    Hm no, ObsTime holds a common date for all dates (1900-01-01), so it just takes care of what time of day it is. Contrary, SPDATE holds the date, but no time of day.

    In what way should it join on the date?

Posting Permissions

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