SQL Server Performance

update every 100 rows with unique number of selected 1000 rows.

Discussion in 'SQL Server 2008 General Developer Questions' started by satyakranth, Jan 12, 2011.

  1. satyakranth New Member

    I have a situation, where I have 1million records in a database.
    I have to select 10,000 rows and for every 100 records I have to update a column with unique value in aloop till first selected 10,000 records are updated.
    commit it and again select next 10,000 and repeat same exercise till I completed 1million.
    I worte some code using select and update, but it is displaying the selected records. Can we bypass the printing records.
    please find below code.create
    table ##counter2 (n int null,dt datetime default getdate() null)declare
    @rows nvarchar(20)declare
    @st_value nvarchar(20)declare
    @st_prefix nvarchar(10)declare
    @st_ID nvarchar(20)set
    @rows = 1set
    @st_prefix = 'S'set
    @st_value = 11000000while
    @rows <> 0begin
    begin
    transaction bselect
    top(10000)* from temp_Table
    set @st_ID = @st_prefix + @st_value;UPDATE
    top(100) temp_TableSET
    StateID = @st_ID where stateID = ' ' and Estatus = 2set
    @rows = @@rowcountset
    @st_value = @st_value + 1;print
    @st_id;insert
    into ##counter2(n) values (@rows)commit
    transaction bend
    Thanks in advance,
    Satya.
  2. FrankKalis Moderator

    Sounds like a homework assignment.
    Anyway, have a look at "Common Table Expression" and "ROW_NUMBER()" in the SQL Server Books Online. These will give you hints about the script
  3. Adriaan New Member

    You have a statement
    SELECT TOP(10000) * FROM tmp_Table
    This returns a list of 10000 rows from tmp_table. So what was your question again?

Share This Page