Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2010
    Posts
    4

    Unanswered: set rowcount issues

    Hi,

    I am trying to use set row count to delete a few lacs of rows from a table. The issue is, within a procedure when I used the code it is deleting randon number of rows. Below is the code snippet from the Procedure:

    declare @RowCount int

    commit

    select getdate()

    select b.Col1,b.Col2,b.Col3,b.Col4 into #tempDeletes
    from phtemp..Table1 a
    ,phtemp..Table2 b
    where a.Col1 =b.Col1
    and a.Col2 =b.Col2
    and a.Col3 =b.Col3
    and a.Col4 =b.Col4
    select @@rowcount

    commit

    set rowcount 50000

    while (@RowCount!=0)
    begin
    begin tran
    select getdate()
    delete phtemp..Table1
    from phtemp..Table1 a, #tempDeletes b
    where a.Col1 =b.Col1
    and a.Col2 =b.Col2
    and a.Col3 =b.Col3
    and a.Col4 =b.Col4
    select @RowCount=@@rowcount
    select @RowCount
    commit
    select getdate()
    end

    set rowcount 0

  2. #2
    Join Date
    Aug 2010
    Posts
    19
    Hi,

    I would advise to create a temp table which is populated with top 2500 (so that no lock escalation occurs), then use this temp table to delete from the origin table.
    Please find my sampel code, first I create a few test rows...

    However, doing this I would recommend you to create a audit table, where you put in some metadata, eg. date, start_id, end_id, etc from the delete statement,
    which such a structure you can find problems easier.

    --drop table #tobedeleted

    -- just to create a empty table for later use
    -- create test data
    select id fid
    into #t1
    from sysobjects

    -- create an empty table with the needed structure
    select top 0 *
    into #tobedeleted
    from #t1 t1



    -- deletion loop
    declare @rowcnt int

    set @rowcnt =1

    while @rowcnt > 0
    begin

    insert into #tobedeleted (fid)
    select top 5 fid from #t1

    delete from #t1
    from #t1 t1
    inner join #tobedeleted t2 on t1.fid = t2.fid

    set @rowcnt = @@rowcount

    end

  3. #3
    Join Date
    Aug 2010
    Posts
    4
    Hi Farhy,

    Thanks for your response, I could dig the issue. My #tempDeletes table was havign duplicate rows which might have caused the issue.

    Regards
    Megha

  4. #4
    Join Date
    Aug 2010
    Posts
    19
    please consider to have a primary key even on temp tables, thats a good design pattern.

Posting Permissions

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