Professional Web Applications Themes

Creating an autonumber query - Microsoft SQL / MS SQL Server

I need a query that will insert an autonumber into a table. I haven't developed the table structure yet because I don't know which datatype to use. I have been trying with a varchar. Let me give an example of what I need the autonumber field to look like. Field 1 ------- 001 002 003 004 005 006 007 008 009 010 011 and so on..... I can't seem to create the code that will do this. Any help would be greatly appreciated. Thanks all....

  1. #1

    Default Creating an autonumber query

    I need a query that will insert an autonumber into a
    table. I haven't developed the table structure yet because
    I don't know which datatype to use. I have been trying
    with a varchar. Let me give an example of what I need the
    autonumber field to look like.

    Field 1
    -------
    001
    002
    003
    004
    005
    006
    007
    008
    009
    010
    011
    and so on.....

    I can't seem to create the code that will do this. Any
    help would be greatly appreciated. Thanks all.
    Andre Guest

  2. #2

    Default Re: Creating an autonumber query

    Can't you just set the field to be an IDENTITY?

    --
    Michael Culley


    "Andre" <com> wrote in message
    news:04b801c35ae5$847d4a40$gbl... 


    Michael Guest

  3. #3

    Default Re: Creating an autonumber query

    >> need a query [sic] that will insert an autonumber into a table. I
    haven't developed the table structure yet because I don't know which
    datatype to use. <<

    What does this "autonumber" mean in terms of your data model? What
    attribute of the reality you are modeing is captured by this? You
    really don't know what you are doing, if you have no idea about the
    table structure.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

  4. #4

    Default Re: Creating an autonumber query

    "Andre" <com> wrote in message
    news:87f601c35afb$9058dda0$gbl... 

    Do you really need the leading zeros? In general data in a database should
    not be formatted, the formatting should be done at the front end.

    If you really do need the zeros then you could create a user defined
    function that returns the max+1 of the field and use the udf as a default
    for that field.

    --
    Michael Culley


    Michael Guest

  5. #5

    Default Re: Creating an autonumber query

    I think using one identity attribute and another calculated like
    TE('0',3-LEN(CONVERT(VARCHAR,id)))+CONVERT(VARCHAR,id)
    would be the best decision if you're really need to have formatted id as DB
    field

    "Andre" <com> wrote in message
    news:04b801c35ae5$847d4a40$gbl... 


    Alex Guest

  6. #6

    Default Re: Creating an autonumber query

    hi andre,

    you can define column as identity field with seed value of 1000. then
    nos. genereated by this identity columns will be
    1000
    1001
    1002
    1003
    1004
    1005
    .....
    .....
    .....
    ..... and so on

    i hope you can, thru this kind of tables
    regards
    Hari Sharma, India



    "Andre" <com> wrote in message news:<87f601c35afb$9058dda0$gbl>... [/ref]
    > because [/ref]
    > the 
    > >
    > >
    > >.
    > >[/ref][/ref]
    hkvats_1999@yahoo.com Guest

  7. #7

    Default Re: Creating an autonumber query

    Andre,
     

    You can use a compiuted column for the formatted value.

    create table t1 (
    c1 int identity,
    c2 varchar(4),
    c3 as right('000' + cast(c1 as varchar(3)), 3)
    )
    go
    insert t1 (c2) values('a')
    insert t1 (c2) values('b')
    insert t1 (c2) values('c')
    select * from t1
    go
    drop table t1

    lindawie Guest

Similar Threads

  1. Creating data query from mySQL stored query!?
    By johnegbert in forum Coldfusion Database Access
    Replies: 2
    Last Post: August 5th, 09:28 PM
  2. Creating a dynamic table from a query
    By marksta80 in forum Macromedia ColdFusion
    Replies: 4
    Last Post: July 31st, 09:24 AM
  3. autonumber question
    By dthatsme in forum Coldfusion - Getting Started
    Replies: 4
    Last Post: March 11th, 10:39 AM
  4. Replies: 1
    Last Post: July 11th, 04:14 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