Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2012
    Posts
    4

    Unanswered: SQL- Table Creation using Cursors

    Hi-
    need some advice-
    I am starting to create a new database table based on an existing dbtable. My existing table has a list of IDís and a date range for each ID.

    For example:
    TableSource
    ID Start DateTime End datetime
    Y10012 01-12-12 13:00:00 01-19-12 13:00:00

    So for this ID, I need my SQL statement to read this table, then create a new table and insert a new row for every second starting from the start date to the end date. I have several idís that span a week at a time. So I am expecting millions and millions of records once I am done.

    End Result:
    MainTable
    ID FullTime
    Y10012 01-12-12 13:00:00
    Y10012 01-12-12 13:00:01
    Y10012 01-12-12 13:00:02
    Y10012 01-12-12 13:00:03
    ....
    Y10012 01-12-19 13:00:00

    Then once it completes reading S1001, it moves on to the next ID and appends it to the table. The date ranges are different for each ID- so it canít be hard coded.

    I am fairly new to SQl, and am not sure if I should try this in SQL using cursors or just do it in Java. Since the database will be sitting in SQL 2008- I figured it would save a lot of importing to just create this table in SQL.

    Any suggestions as to how I might start this, and if Transact SQL is the right way to go about this?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can I just ask why you would want to do this?

    I reckon it can be achieved using a set-based SQL method (i.e. no loops). Hint: a numbers table
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This can certainly be done using a numbers table (or maybe better using a CTE), but like gvee I have to question WHY you want to create this table. It seems like a poor practice to me, wasting lots of disk and processing time that could probably be easily replaced by some smart coding.

    Keep in mind that just because something can be done, that does not mean that it should be done!

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

  4. #4
    Join Date
    Mar 2012
    Posts
    4
    Thanks for your response.
    I am not that familiar yet with SQL- and am just learning.

    I am aware that this is a HUGE amount of data. However- there really is no way around it. It is for a large scale research project. Each second of the day will serve as a uniquie ID (Y1001_DateTime)- which will join to data collected for each second/ each minute and each hour for each study ID.

    So- no cursors then?

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You can get code to create a numbers table from this thread: http://www.dbforums.com/microsoft-sq...day-frida.html

    Untested code:
    Code:
    SELECT x.id
         , DateAdd(ss, numbers.number, x.start_datetime) As intervals
    FROM   (
            SELECT id
                 , start_datetime
                 , DateDiff(ss, start_datetime, end_datetime) As seconds
            FROM   dbo.tablesource
           ) As x
     INNER
      JOIN dbo.numbers
        ON numbers.number BETWEEN 0 AND x.seconds
    George
    Home | Blog

  6. #6
    Join Date
    Mar 2012
    Posts
    4
    wow- thanks a lot!
    I'll try to implement this code and see how it works
    much appreciated!

Posting Permissions

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