Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2007
    Posts
    7

    Unanswered: Mass updates in SQL server

    Does anyone know what the best way to do mass updates in SQL server is? I am currently using the methodology suggested in this article

    http://www.tek-tips.com/faqs.cfm?fid=3141

    But the article is assuming that once I update a field it is going to have a value that is NOT NULL. So I can loop through and update the rows that have a NOT NULL value. But my updated rows do contain NULL values, in this case what is the best way to go about this???

    ***************************************
    Here is my code. I want to avoid using Upd_flag becos
    after the following code runs I need to reset that flag
    before I run my next query
    ***************************************

    --Set rowcount to 50000 to limit number of inserts per batch
    Set rowcount 50000

    --Declare variable for row count
    Declare @rc int
    Set @rc=50000

    While @rc=50000
    Begin

    Begin Transaction

    --Use tablockx and holdlock to obtain and hold
    --an immediate exclusive table lock. This usually
    --speeds the insert because only one lock is needed.


    update t_PGBA_DTL With (tablockx, holdlock)
    SET t_PGBA_DTL.procedur = A.[Proc code],
    t_PGBA_DTL.Upd_flag = 1
    FROM t_PGBA_DTL
    INNER JOIN CPT_HCPCS_I9_PROC_CODES A
    ON t_PGBA_DTL.PROC_CD
    = A.[Proc code]
    WHERE t_PGBA_DTL.Upd_flag = 0


    --Get number of rows updated
    --Process will continue until less than 50000
    Select @rc=@@rowcount

    --Commit the transaction
    Commit
    End

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    where t_PGBA_DTL.procedur != A.[Proc code]

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    no, no, no, it doesn't work that way

    Do you have an identity column on the table?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jun 2007
    Posts
    7
    Thank you Brett. I just added the Identity column. I just needed confirmation that its the best way to go about this. Thank you again.

    -soumya

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    DECALRE @x int, @y int, @z int
    SELECT @y = SELECT COUNT(*) FROM TABLE
    SELECT @z = @y/10, @x = @y/10
    WHILE @x < @y
    BEGIN
    UPDATE Table SET...
    WHERE IdentCol < @x
    SET @x = @x + @z
    END
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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