Looping in SQL

5 pts.
Tags:
.NET Framework 3.5
Looping
SQL Server
SQL Server 2005
dear, i have one table with more than (16000) rows. now i should add on column with "computed column" as a result for other columns, so i already create the new column with correct statment but how can i make this statment excute for all pervious rows. to be exact: the new column should contain result for all of the old rows, so should i make it by SP or looping in table???

Software/Hardware used:
ms sql, .net
ASKED: February 23, 2011  7:18 AM
UPDATED: February 23, 2011  8:55 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Should be able to do something like this:

UPDATE table_name
SET new_column = (calculated value)
WHERE new_column is NULL

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    Can't you just fire an UPDATE statement without conditions ?
    65,110 pointsBadges:
    report
  • Kccrosser
    16,000 rows isn't a very large table, so just running the UPDATE statement is probably the easiest way in this case. For large tables, I prefer to do mass updates through a script or stored procedure as block transactions, committing the transactions every N thousand rows. The objective is to try to keep the rollback segments reasonably small, and yet not commit too often. Huge rollback segments can seriously affect system performance and cause blocking issues if the system is in use by others. Committing too often (like on every row) generally causes excessive overhead and slows the process. I usually use a script like:
    declare @commitcount int;
    declare @irecord int;
    
    set @commitcount = 5000;
    set @irecord = 0;
    
    declare cur cursor for select ... (records to be updated);
    open cur;
    fetch next from cur into ... (local variables as needed);
    begin transaction;
    while @@fetch_status = 0
    begin
        update ... (update statement to fix this record);
        set @irecord = @irecord + 1;
        if @irecord % @commitcount = 0
        begin
            commit;
            begin transaction;
        end;
    end;
    commit;   -- don't forget to finish last pending transaction
    close cur;
    deallocate cur;
    The choice of a value for @commitcount is determined by what you are updating (number, size of rows/columns), your server capabilities, and the size of the table. If you are updating a single integer column in a table, you might want to make @commitcount very large, like 10000. If you are updating multiple varchar columns, 500 or 1000 might be a better choice.
    3,830 pointsBadges:
    report
  • Denny Cherry
    If you are using a computed column, there shouldn't need to be any update statement run. Did you create a new column that you'll do the calculation to get the value in manually, or are you actually using a computed column within SQL Server?
    65,450 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following