Professional Web Applications Themes

Oracle RowNum Equivalent - Microsoft SQL / MS SQL Server

Hello, I'm looking for an equivalent of the Oracle RowNum function in SQL Server, or some other method of solving this problem. In the DDL below I'm inserting three values - Rod, Jane and Freddy with a type of Rainbow. I want to re-insert them again with a type of Entertainers but I want them to have unique identity keys. I'd rather not drop the table and recreate it with an identity field as I've got 10 million rows to do and it will take all day. The design is just fictitious but it's an example of the kind of ...

  1. #1

    Default Oracle RowNum Equivalent

    Hello, I'm looking for an equivalent of the Oracle RowNum function in SQL
    Server, or some other method of solving this problem.

    In the DDL below I'm inserting three values - Rod, Jane and Freddy with a
    type of Rainbow. I want to re-insert them again with a type of Entertainers
    but I want them to have unique identity keys.

    I'd rather not drop the table and recreate it with an identity field as I've
    got 10 million rows to do and it will take all day. The design is just
    fictitious but it's an example of the kind of thing I want to do with my
    real table.

    I was hoping there's a way to do this quickly using a select max() and
    adding on a rownum variable of some sort.

    Any help much appreciated.

    Thanks, Kevin.

    set nocount on

    drop table test
    create table test (
    id int,
    typ varchar(14),
    dsc varchar(8)
    )

    insert into test values (1,'Rainbow','Rod')
    insert into test values (2,'Rainbow','Jane')
    insert into test values (3,'Rainbow','Freddy')

    -- the next line is a syntax error as there is no rownum
    -- and I'm also not sure about the select max() syntax in this query..
    insert into test (id,typ,dsc)
    select (select max(id)+rownum from test),'Entertainers',dsc from test
    where typ='Rainbow'

    select * from test


    Kevin Munro Guest

  2. #2

    Default Re: Oracle RowNum Equivalent

    Thanks for this David.

    INSERT INTO test (id, typ, dsc)
    SELECT (SELECT MAX(id) FROM test)+
    (SELECT COUNT(*)
    FROM test AS T
    WHERE id<=test.id),
    'Entertainers',dsc
    FROM test
    WHERE typ='Rainbow'

    Gives same results, is the 'WHERE typ=test.typ' in for speed?

    Kevin.


    "David Portas" <REMOVE_BEFORE_REPLYING_dportasacm.org> wrote in message
    news:%23OrcuefRDHA.704tk2msftngp13.phx.gbl...
    > Assuming that ID is unique:
    >
    > INSERT INTO test (id, typ, dsc)
    > SELECT (SELECT MAX(id) FROM test)+
    > (SELECT COUNT(*)
    > FROM test AS T
    > WHERE typ=test.typ
    > AND id<=test.id),
    > 'Entertainers',dsc
    > FROM test
    > WHERE typ='Rainbow'
    >
    > --
    > David Portas
    > ------------
    > Please reply only to the newsgroup
    > --
    >
    >
    >

    Kevin Munro Guest

  3. #3

    Default Re: Oracle RowNum Equivalent

    If you want to be sure the IDs are generated sequentially without gaps then
    you need the additional predicate typ=test.typ. Without it you could get
    this:

    INSERT INTO TEST VALUES (1,'A','Rod')
    INSERT INTO TEST VALUES (2,'B','Jane')
    INSERT INTO TEST VALUES (3,'A','Freddy')

    INSERT INTO test (id, typ, dsc)
    SELECT (SELECT MAX(id) FROM test)+
    (SELECT COUNT(*)
    FROM test AS T
    WHERE id<=test.id),
    'C',dsc
    FROM test
    WHERE typ='A'

    SELECT * FROM test

    id typ dsc
    ----------- -------------- --------
    1 A Rod
    2 B Jane
    3 A Freddy
    4 C Rod
    6 C Freddy

    If you don't mind gaps then your version is fine. If you have a unique index
    on ID then I expect the performance is about the same with either query.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Portas Guest

  4. #4

    Default Re: Oracle RowNum Equivalent

    On second thoughts. Use the query I posted, provided you have an index on
    Typ. Your version is likely to force a table scan.

    INSERT INTO test (id, typ, dsc)
    SELECT (SELECT MAX(id) FROM test)+
    (SELECT COUNT(*)
    FROM test AS T
    WHERE typ=test.typ
    AND id<=test.id),
    'Entertainers',dsc
    FROM test
    WHERE typ='Rainbow'

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Portas Guest

  5. #5

    Default Re: Oracle RowNum Equivalent

    Thanks for this, I've understood your other post also.

    Kevin.

    "David Portas" <REMOVE_BEFORE_REPLYING_dportasacm.org> wrote in message
    news:%23o2FT4fRDHA.3192tk2msftngp13.phx.gbl...
    > On second thoughts. Use the query I posted, provided you have an index on
    > Typ. Your version is likely to force a table scan.
    >
    > INSERT INTO test (id, typ, dsc)
    > SELECT (SELECT MAX(id) FROM test)+
    > (SELECT COUNT(*)
    > FROM test AS T
    > WHERE typ=test.typ
    > AND id<=test.id),
    > 'Entertainers',dsc
    > FROM test
    > WHERE typ='Rainbow'
    >
    > --
    > David Portas
    > ------------
    > Please reply only to the newsgroup
    > --
    >
    >
    >

    Kevin Munro Guest

Similar Threads

  1. sql server equivalent procedure in Oracle
    By reenaroy in forum Coldfusion Database Access
    Replies: 4
    Last Post: November 9th, 05:18 AM
  2. Equivalent of Oracle utl_raw.bit_and , alias catalog
    By Thomas Theakanath in forum IBM DB2
    Replies: 1
    Last Post: August 26th, 04:03 PM
  3. How to do equivalent to Crypt:CBC of Perl in Oracle pl/sql
    By Ron Reidy in forum PERL Miscellaneous
    Replies: 1
    Last Post: July 29th, 05:35 AM
  4. Oracle/SQL Server and Informix Equivalent Command
    By shahid.mehmood in forum Informix
    Replies: 3
    Last Post: July 19th, 01:12 AM
  5. Replies: 3
    Last Post: December 6th, 02:36 PM

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