Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2013
    Posts
    14

    Unanswered: Loop for updating a million rows.

    Hi All,

    We need to update time stamps on one of our tables which has a column with timestamp generated from getdate() function.

    The plan to update is by dateadd function.

    i understand that i will have to do this in batch so i do not fill my tran log.

    my plan is to do this sequentially for all rows
    =>copy 1000(just an assumption) rows from test_Table to TempDB.testing
    =>update the rows in TempDB.testing
    =>return them back

    => repeat the process.

    Code:
    set rowcount 1000
    
    print "Moving 1000 rows at a time"
    
    select * into TempDB.testing from test_Table                                                                                                                                                                            
    go 
    
    update table TempDB.testing set time1=dateadd(hour,3,time1)
    go
    
    select * into IMPdb.test_table from TempDB.testing
    go
    
    truncate TempDB.testing
    My question is how do i loop this to pick next 1000 lines once it is done updating the first 1000 or is this approach even right?

    Thanks for your help.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The first question that enters my mind is "Why?"... What purpose does adding an arbitrary value like three hours to every datetime serve?

    The next question would have to be: Are your timestamps unique, or could there be duplicate values?

    The next question would be: Could a Computed Column serve your needs?

    I can't answer your question very well without a lot more insight. After I understand your needs better, I can probably give you a relatively clean and simple answer but for right now I'm struggling!

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

  3. #3
    Join Date
    Dec 2013
    Posts
    14
    Hi Pat,

    Thanks for taking out time to look into this.
    1) I am trying to convert PDT to EDT. So adding three hours is the easiest solution in my mind.

    2)No the time stamps are not unique and the format is => Jul 18 2014 1:32PM
    After the dateadd function it will look like Jul 18 2014 4:32PM

    3) Sadly computed column will not be possible as i cannot add another column to the existing structure and have to modify the existing data and the value received in the columns i wish to update come from the getdate() function and is not calculated.

    i was just reading about

    Code:
    set rowcount 1000
    go
    set @counter = 0 -- declare a 
    variable
    while ( @counter < 10 ) begin
    ** -- sql 
    statement
    ** set @counter = @counter + 1
    end
    Thank you once again

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are a lot of potential problems, but the simplest solution is to take downtime and update the entire table in one UPDATE. This will effectively take the table offline while the update is being done, but it will generate the fewest problems in terms of possible lost data file overhead, etc.

    This steps would be:
    1. Backup your database
    2. Note the existing recovery model
    3. Set SIMPLE recovery model
    4. Update the column by adding 3 hours
    5. Restore the previous recovery model
    6. Make a second backup
    This will require down time, but it will not lose any data and it is very efficient in terms of minimizing log use and other resources.

    There are probably ways to do this update without requiring downtime, but I'd still need to know more about the constraints and options that you've got.

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

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As a side note, if you are going to go to the trouble to change the timezone then I would VERY STRONGLY argue that you should change the time to UTC instead of ET. That would get past the problems of DST, and would allow every location to easily display the times in local time instead of "foreign time" of another time zone.

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

Posting Permissions

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