Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2004
    Posts
    492

    Unanswered: Cursor with a chip on it's shoulder

    hey there...

    I'm trafersing through a table using a where-clause, inserting data into another. The where-clause excludes rows that are already inserted (some int value).

    The cursor I use has a inner join so that it'll result in multiple inserts in one go (a house has one or more person/s in it). After the insert the original is updated so it won't be used again.

    I haven't replicated this, but sql2000 seems to miss out on records occasionaly: not all of the persons were inserted.

    Now, I'm thinking to remove the cursor and/or do the update after all inserts are done. Still, I'd like to know if there's some re-check the cursor does half-way down its insert. Any idea what's wrong?

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    I've replicated the problem:
    Code:
    use monkey
    go
    
    set nocount on
    go
    
    -- basic sample data
    create table t1 (myint int, mystatus int)
    insert into t1 values(1, 1)
    insert into t1 values(2, 1)
    insert into t1 values(3, 1)
    insert into t1 values(4, 1)
    go
    
    create table t2 (myint int, myvalue varchar(3))
    insert into t2 values(1, 'A')
    insert into t2 values(1, 'B')
    insert into t2 values(2, 'C')
    insert into t2 values(3, 'D')
    insert into t2 values(4, 'E')
    go
    
    -- target table
    create table t3 (myint int, myvalue varchar(3))
    go
    
    -- local var
    declare @myint int
    declare @myvalue varchar(3)
    
    declare mycursor cursor fast_forward local
    for select t1.myint, t2.myvalue from t1 inner join t2 on (t1.myint = t2.myint) where t1.mystatus = 1
    open mycursor
    fetch next from mycursor into @myint, @myvalue
    while (@@fetch_status = 0)
    begin
      insert into t3 values (@myint, @myvalue)
    
      update t1 set mystatus = 2 where myint = @myint 
    
      fetch next from mycursor into @myint, @myvalue
    end
    
    -- show it
    select * from t3
    
    deallocate mycursor
    
    go
    drop table t1
    go
    drop table t2
    go
    drop table t3
    go

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    does this code that does the same thing have the same problem?

    Its hard to say what is wrong here. Is myInt unique in t2 or is it just a foreign key?

    Code:
    insert into t3 (myint, myvalue) 
    select t1.myint, t2.myvalue 
    from t1 inner 
    join t2 
    on t1.myint = t2.myint
    
    update t1 set mystatus = 2 
    FROM t1 as t1
    join t2 
    on t1.myint = t2.myint
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Run this again, and show the Execution plan. The cursor query is being executed for each fetch. I think you need to look into adding some restrictions on the cursor, either static, or readonly, not sure which.

  5. #5
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    Always be carefull when you are updating the base table your cursor is using:

    Code:
    -- basic sample data
    create table t1 (myint int)
    insert into t1 values(1)
    insert into t1 values(2)
    insert into t1 values(3)
    insert into t1 values(4)
    go
    
    -- local var
    declare @myint int
    
    declare mycursor cursor fast_forward local
    for select myint from t1
    open mycursor
    fetch next from mycursor into @myint
    while (@@fetch_status = 0)
    begin
    
    	SELECT @myint
    	DELETE FROM [t1] WHERE myint IN (3,4)
    
      fetch next from mycursor into @myint
    end
    deallocate mycursor
    
    go
    drop table t1
    go
    In your example if you create a temp table and use it as the base table it will work:

    Code:
    -- basic sample data
    create table t1 (myint int, mystatus int)
    insert into t1 values(1, 1)
    insert into t1 values(2, 1)
    insert into t1 values(3, 1)
    insert into t1 values(4, 1)
    go
    
    create table t2 (myint int, myvalue varchar(3))
    insert into t2 values(1, 'A')
    insert into t2 values(1, 'B')
    insert into t2 values(2, 'C')
    insert into t2 values(3, 'D')
    insert into t2 values(4, 'E')
    go
    
    -- target table
    create table t3 (myint int, myvalue varchar(3))
    go
    
    CREATE TABLE #temp (myint int, myvalue varchar(3))
    go
    
    INSERT INTO #temp
    select t1.myint, t2.myvalue from t1 inner join t2 on (t1.myint = t2.myint) where t1.mystatus = 1
    
    -- local var
    declare @myint int
    declare @myvalue varchar(3)
    
    declare mycursor cursor fast_forward local
    for SELECT myint, myvalue FROM #temp
    open mycursor
    fetch next from mycursor into @myint, @myvalue
    while (@@fetch_status = 0)
    begin
      insert into t3 values (@myint, @myvalue)
    
      update t1 set mystatus = 2 where myint = @myint 
    
      fetch next from mycursor into @myint, @myvalue
    end
    
    -- show it
    select * from t3
    
    deallocate mycursor
    
    go
    drop table t1
    go
    drop table t2
    go
    drop table t3
    go
    DROP TABLE #temp
    go

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK, any thought about losing the cursor?
    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.

  7. #7
    Join Date
    Feb 2004
    Posts
    492
    I realized that the update conflicts with the cursor, but it surprised me that it does. I didn't expect this, somehow the cursor magically must be 'aware' that it processed certain rows, but not others. Thinking about this, I guess I got struck by lightning and had a though that that's what this cursor does and I'm joining on a cursor level, so... Aha!

    Still, I wonder the inner workings of tracking the whole lot.

    For that matter, changing to a static cursor would fix it, unfortunately, the actual number of records is quite large so I don't see this as a solution.

    The other option is to rewrite the cursor to just select the id's and to write a full insert:
    Code:
    declare mycursor2 cursor fast_forward local
    for select t1.myint from t1 where t1.mystatus = 1
    ...
    insert into t3 (myint, myvalue) (select t1.myint, t2.myvalue from t1 inner join t2 on (t1.myint = t2.myint) where t1.myint = @myint)
    ...
    or indeed, just loose the cursor...

  8. #8
    Join Date
    Sep 2005
    Posts
    161
    Why not rewrite it as a set based query instead of using a cursor?

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    sometimes i think my posts are only visible to me.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What was that? Did you hear something?
    Musta' been the wind....
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Thrasymachus
    sometimes i think my posts are only visible to me.

    Perhaps that could be true
    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
  •