Professional Web Applications Themes

How can I do this (modified) without a cursor - Microsoft SQL / MS SQL Server

this is a modification of a previous post. Thanks Jacco,Tom,Anith and Steve for your help. The solution to my last post was this.. /***************** update tablex set [sequence] = ( select count(*) from tablex t where t.stulink = tablex.stulink and t.enterdate >= tablex.enterdate ) go Steve Kass Drew University ****************/ The modification to my question has to do with the fact that the enterdate could be duplicated. See below... I have a proc called resequenceX. My table has three fields stulink,[sequence] and enterdate. The data looks like this... stulink [sequence] enterdate 1 null 11/01/2002 1 null 11/01/2002 <--******Note the duplicate ...

  1. #1

    Default How can I do this (modified) without a cursor

    this is a modification of a previous post. Thanks Jacco,Tom,Anith and
    Steve for your help. The solution to my last post was this..

    /*****************

    update tablex set
    [sequence] = (
    select count(*) from tablex t
    where t.stulink = tablex.stulink
    and t.enterdate >= tablex.enterdate
    )
    go

    Steve Kass
    Drew University

    ****************/

    The modification to my question has to do with the fact that the
    enterdate could be duplicated. See below...


    I have a proc called resequenceX. My table has three fields
    stulink,[sequence] and enterdate.
    The data looks like this...
    stulink [sequence] enterdate
    1 null 11/01/2002
    1 null 11/01/2002 <--******Note the duplicate date
    1 null 11/03/2002
    2 null 11/04/2002
    2 null 11/05/2002
    2 null 11/06/2002

    It needs to look like this...
    (most recent stulink,enterdate gets first sequence#)
    (grouping by stulink)
    stulink [sequence] enterdate
    1 3 11/01/2002
    1 2 11/01/2002 <--*******Note dup date needs a unique sequence#.
    1 1 11/03/2002
    2 3 11/04/2002
    2 2 11/05/2002
    2 1 11/06/2002

    When I ran Steve's code (which did exactly what I was previously
    asking for) on data that contained duplicate enterdates on the same
    stulink, it would end up like this
    stulink [sequence] enterdate
    1 3 11/01/2002
    1 3 11/01/2002 <--******* wrong
    1 1 11/03/2002
    2 3 11/04/2002
    2 2 11/05/2002
    2 1 11/06/2002

    Does anyone have an idea of how I can get the proper sequence values
    w/o using a cursor?

    I usually run the proc on tables over 300,000 rows
    The number of different stulinks and enterdates are infinite.

    Thanks for your help!




    Below is all you should need to recreate schema/data

    CREATE TABLE [dbo].[tableX] (
    [stulink] [int] NOT NULL ,
    [sequence] [int] NULL ,
    [enterdate] [datetime] NOT NULL
    ) ON [PRIMARY]
    GO


    insert into tablex values (1,null,'11/01/2003')
    insert into tablex values (1,null,'11/01/2003')
    insert into tablex values (1,null,'11/03/2003')
    insert into tablex values (2,null,'11/04/2003')
    insert into tablex values (2,null,'11/05/2003')
    insert into tablex values (2,null,'11/06/2003')

    Here is my proc...
    /************
    Start PROC
    *************/

    CREATE proc resequenceX table char(6), column varchar (25)
    as
    DECLARE stulink varchar(12),
    tmp varchar(12),
    pk varchar(10),
    num varchar(10),
    cmd varchar(800)
    set num = '1'
    set tmp = '000000'
    --1) backup this
    --exec sp_dboption 'pubs', 'SELECT INTO/BULKCOPY',true
    set cmd = 'select * into '+table+'_bak from '+table
    print cmd
    exec (cmd)

    --2) select into this_pk where not equal
    set cmd = 'select * into '+table+'_pk from '+table+' where 2=3'
    print cmd
    exec (cmd)

    --3) add pk column to this_pk
    set cmd = 'ALTER TABLE '+table+'_pk ADD pk int identity (1,1)'
    print cmd
    exec (cmd)

    --4) insert into this_pk from this where equal
    set cmd = 'insert into '+table+'_pk select * from '+table+' where
    1=1'
    print cmd
    exec (cmd)

    --5) create index
    set cmd = 'CREATE NONCLUSTERED INDEX ['+table+'_pk0] ON
    [dbo].['+table+'_pk]([pk])'
    print cmd
    exec (cmd)
    print 'index created'

    exec ('declare maint_copy cursor for select stulink, pk from
    '+table+'_pk
    order by stulink, '+column+' DESC')
    entrydate DESC
    DECLARE count int
    SELECT count = 1

    OPEN maint_copy
    FETCH NEXT FROM maint_copy INTO stulink, pk
    WHILE (fetch_status <> -1)
    BEGIN
    IF (fetch_status <> -2)
    BEGIN
    IF (tmp = stulink)
    BEGIN
    SET num = num + 1
    END
    ELSE
    BEGIN
    SET num = 1
    END
    --PRINT(num)
    set cmd = 'UPDATE '+table+'_pk set sequence ='+num+' WHERE pk =
    '+pk
    print cmd
    exec (cmd)

    SET tmp = stulink
    END
    FETCH NEXT FROM maint_copy INTO stulink, pk
    SELECT count = count + 1
    END
    set cmd = 'Count = ' + str(count)
    print cmd
    CLOSE maint_copy
    DEALLOCATE maint_copy

    --6a) drop index
    set cmd = 'drop INDEX '+table+'_pk.'+table+'_pk0'
    print cmd
    exec (cmd)

    --6) drop pk column from this_pk
    --alter table this_pk drop column pk
    set cmd = 'ALTER TABLE '+table+'_pk drop column pk'
    print cmd
    exec (cmd)

    --7) drop this
    set cmd = 'drop table '+table
    print cmd
    exec (cmd)

    --8) rename this_pk to this
    set cmd = 'sp_rename '+table+'_pk, '+table
    print cmd
    exec (cmd)

    /************
    End PROC
    *************/

    Run Proc like this
    resequenceX tableX, enterdate

    this query..
    select * from tableX

    will output this..
    stulink [sequence] enterdate
    ----------- ----------- ---------------------------
    1 3 2003-11-01 00:00:00.000
    1 2 2003-11-01 00:00:00.000
    1 1 2003-11-03 00:00:00.000
    2 3 2003-11-04 00:00:00.000
    2 2 2003-11-05 00:00:00.000
    2 1 2003-11-06 00:00:00.000

    Just like it should be.
    The question is how can I do this without a cursor? (read faster)
    I usually run the proc on tables over 300,000 rows
    The number of different stulinks and enterdates are infinite.

    Thanks for your help!
    bryan jones Guest

  2. #2

    Default Re: How can I do this (modified) without a cursor

    Use a (temporary) identity column:

    ALTER TABLE tablex ADD id_temp INT IDENTITY
    GO
    update tablex set
    [sequence] = (
    select count(*) from tablex t
    where t.stulink = tablex.stulink
    and t.enterdate >= tablex.enterdate
    and t.id_temp >= tablex.id_temp
    )
    GO
    ALTER TABLE tablex DROP COLUMN id_temp
    GO

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "bryan jones" <utefan001> wrote in message
    news:4b5dd0f7.0307010750.5bbea2f4posting.google.c om...
    > this is a modification of a previous post. Thanks Jacco,Tom,Anith and
    > Steve for your help. The solution to my last post was this..
    >
    > /*****************
    >
    > update tablex set
    > [sequence] = (
    > select count(*) from tablex t
    > where t.stulink = tablex.stulink
    > and t.enterdate >= tablex.enterdate
    > )
    > go
    >
    > Steve Kass
    > Drew University
    >
    > ****************/
    >
    > The modification to my question has to do with the fact that the
    > enterdate could be duplicated. See below...
    >
    >
    > I have a proc called resequenceX. My table has three fields
    > stulink,[sequence] and enterdate.
    > The data looks like this...
    > stulink [sequence] enterdate
    > 1 null 11/01/2002
    > 1 null 11/01/2002 <--******Note the duplicate date
    > 1 null 11/03/2002
    > 2 null 11/04/2002
    > 2 null 11/05/2002
    > 2 null 11/06/2002
    >
    > It needs to look like this...
    > (most recent stulink,enterdate gets first sequence#)
    > (grouping by stulink)
    > stulink [sequence] enterdate
    > 1 3 11/01/2002
    > 1 2 11/01/2002 <--*******Note dup date needs a unique sequence#.
    > 1 1 11/03/2002
    > 2 3 11/04/2002
    > 2 2 11/05/2002
    > 2 1 11/06/2002
    >
    > When I ran Steve's code (which did exactly what I was previously
    > asking for) on data that contained duplicate enterdates on the same
    > stulink, it would end up like this
    > stulink [sequence] enterdate
    > 1 3 11/01/2002
    > 1 3 11/01/2002 <--******* wrong
    > 1 1 11/03/2002
    > 2 3 11/04/2002
    > 2 2 11/05/2002
    > 2 1 11/06/2002
    >
    > Does anyone have an idea of how I can get the proper sequence values
    > w/o using a cursor?
    >
    > I usually run the proc on tables over 300,000 rows
    > The number of different stulinks and enterdates are infinite.
    >
    > Thanks for your help!
    >
    >
    >
    >
    > Below is all you should need to recreate schema/data
    >
    > CREATE TABLE [dbo].[tableX] (
    > [stulink] [int] NOT NULL ,
    > [sequence] [int] NULL ,
    > [enterdate] [datetime] NOT NULL
    > ) ON [PRIMARY]
    > GO
    >
    >
    > insert into tablex values (1,null,'11/01/2003')
    > insert into tablex values (1,null,'11/01/2003')
    > insert into tablex values (1,null,'11/03/2003')
    > insert into tablex values (2,null,'11/04/2003')
    > insert into tablex values (2,null,'11/05/2003')
    > insert into tablex values (2,null,'11/06/2003')
    >
    > Here is my proc...
    > /************
    > Start PROC
    > *************/
    >
    > CREATE proc resequenceX table char(6), column varchar (25)
    > as
    > DECLARE stulink varchar(12),
    > tmp varchar(12),
    > pk varchar(10),
    > num varchar(10),
    > cmd varchar(800)
    > set num = '1'
    > set tmp = '000000'
    > --1) backup this
    > --exec sp_dboption 'pubs', 'SELECT INTO/BULKCOPY',true
    > set cmd = 'select * into '+table+'_bak from '+table
    > print cmd
    > exec (cmd)
    >
    > --2) select into this_pk where not equal
    > set cmd = 'select * into '+table+'_pk from '+table+' where 2=3'
    > print cmd
    > exec (cmd)
    >
    > --3) add pk column to this_pk
    > set cmd = 'ALTER TABLE '+table+'_pk ADD pk int identity (1,1)'
    > print cmd
    > exec (cmd)
    >
    > --4) insert into this_pk from this where equal
    > set cmd = 'insert into '+table+'_pk select * from '+table+' where
    > 1=1'
    > print cmd
    > exec (cmd)
    >
    > --5) create index
    > set cmd = 'CREATE NONCLUSTERED INDEX ['+table+'_pk0] ON
    > [dbo].['+table+'_pk]([pk])'
    > print cmd
    > exec (cmd)
    > print 'index created'
    >
    > exec ('declare maint_copy cursor for select stulink, pk from
    > '+table+'_pk
    > order by stulink, '+column+' DESC')
    > entrydate DESC
    > DECLARE count int
    > SELECT count = 1
    >
    > OPEN maint_copy
    > FETCH NEXT FROM maint_copy INTO stulink, pk
    > WHILE (fetch_status <> -1)
    > BEGIN
    > IF (fetch_status <> -2)
    > BEGIN
    > IF (tmp = stulink)
    > BEGIN
    > SET num = num + 1
    > END
    > ELSE
    > BEGIN
    > SET num = 1
    > END
    > --PRINT(num)
    > set cmd = 'UPDATE '+table+'_pk set sequence ='+num+' WHERE pk =
    > '+pk
    > print cmd
    > exec (cmd)
    >
    > SET tmp = stulink
    > END
    > FETCH NEXT FROM maint_copy INTO stulink, pk
    > SELECT count = count + 1
    > END
    > set cmd = 'Count = ' + str(count)
    > print cmd
    > CLOSE maint_copy
    > DEALLOCATE maint_copy
    >
    > --6a) drop index
    > set cmd = 'drop INDEX '+table+'_pk.'+table+'_pk0'
    > print cmd
    > exec (cmd)
    >
    > --6) drop pk column from this_pk
    > --alter table this_pk drop column pk
    > set cmd = 'ALTER TABLE '+table+'_pk drop column pk'
    > print cmd
    > exec (cmd)
    >
    > --7) drop this
    > set cmd = 'drop table '+table
    > print cmd
    > exec (cmd)
    >
    > --8) rename this_pk to this
    > set cmd = 'sp_rename '+table+'_pk, '+table
    > print cmd
    > exec (cmd)
    >
    > /************
    > End PROC
    > *************/
    >
    > Run Proc like this
    > resequenceX tableX, enterdate
    >
    > this query..
    > select * from tableX
    >
    > will output this..
    > stulink [sequence] enterdate
    > ----------- ----------- ---------------------------
    > 1 3 2003-11-01 00:00:00.000
    > 1 2 2003-11-01 00:00:00.000
    > 1 1 2003-11-03 00:00:00.000
    > 2 3 2003-11-04 00:00:00.000
    > 2 2 2003-11-05 00:00:00.000
    > 2 1 2003-11-06 00:00:00.000
    >
    > Just like it should be.
    > The question is how can I do this without a cursor? (read faster)
    > I usually run the proc on tables over 300,000 rows
    > The number of different stulinks and enterdates are infinite.
    >
    > Thanks for your help!

    Jacco Schalkwijk Guest

Similar Threads

  1. date record was last modified. Help!!!!
    By Joe Science in forum Coldfusion - Getting Started
    Replies: 1
    Last Post: March 23rd, 07:52 PM
  2. RDS and Date Modified any better in MX 7?
    By momnotmom in forum Coldfusion Server Administration
    Replies: 0
    Last Post: March 10th, 11:02 PM
  3. cursor 200-problem on mac but not pc? how to swap cursor image?
    By nickelsock webforumsuser@macromedia.com in forum Macromedia Director Lingo
    Replies: 5
    Last Post: August 2nd, 10:58 AM
  4. Change the "web hand" cursor in normal arrow cursor?
    By FB1976 in forum Adobe Dreamweaver & Contribute
    Replies: 1
    Last Post: July 9th, 07:15 PM
  5. Optimistic concurrency ... failed. The row was modified outside of this cursor
    By mark baekdal in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: June 30th, 11:02 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139