Results 1 to 7 of 7

Thread: AutoNumber with SQL

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Sep 2005
    Location
    Madison, WI
    Posts
    136

    AutoNumber with SQL

    HI,

    I am wondering how to get an autonumber field with SQL, just like in Access. Is this possible?

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: AutoNumber with SQL

    If you mean SQL Server (which is one of many DBMS's which support SQL), you need to set it to be an Identity field.

    In the table design in Enterprise Manager you can set the Identity Seed (the number to start at), and the Indentity Increment (the number to add to find the 'next' number). Both of these are normally set to 1.

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Sep 2005
    Location
    Madison, WI
    Posts
    136

    Talking Re: AutoNumber with SQL

    si_,

    I have more questions....
    1. What does 'Yes(Not for Replication)' mean?
    2. I need to create this column in a temp table, what is the code to set the indenity seed and increment?
    3. And, this is really dumb I know, but when I create a temp table, where do I do that? I can attach the code to a button, right? Or am I just completely missing the SQL query boat?

    Thanks

  4. #4
    Banned randem's Avatar
    Join Date
    Oct 2002
    Location
    Maui, Hawaii
    Posts
    11,385

    Re: AutoNumber with SQL

    callydata,

    You are missing the boat. Use an SQL query to create a temp table.

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: AutoNumber with SQL

    In most cases I agree, but it depends on what callydata is doing with the table.

    To answer the questions:
    1) Unless you are replicating the database (to another DB server), this is fine - see the help if you want details of what it means.
    2) The default is 1 and 1, I'm afraid I don't know how to set i differently as I've never needed to. The T-SQL guide in the help for Create Table should show the syntax
    3) As long as you have a database connection set up in code (not a data control) you can run it from wherever you like in your program. If you don't know how to do this, there is an ADO Tutorial link in my signature that should help.

  6. #6
    Fanatic Member d2005's Avatar
    Join Date
    Aug 2005
    Location
    ireland
    Posts
    620

    Re: AutoNumber with SQL

    check out the thread on this page auto increment key field ,
    some people helped me with a similar problem,
    it works 60% of the time, all the time.

  7. #7
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: AutoNumber with SQL

    Is this MS SQL Server??

    From BOL...

    Examples
    A. Reset the current identity value, if needed
    This example resets the current identity value, if needed, of the jobs table.

    USE pubs
    GO
    DBCC CHECKIDENT (jobs)
    GO

    B. Report the current identity value
    This example reports the current identity value in the jobs table, and does not correct the identity value, if incorrect.

    USE pubs
    GO
    DBCC CHECKIDENT (jobs, NORESEED)
    GO

    C. Force the current identity value to 30
    This example forces the current identity value in the jobs table to a value of 30.

    USE pubs
    GO
    DBCC CHECKIDENT (jobs, RESEED, 30)
    GO
    I thought I had read about how to set the increment - but apparently I cannot find that right now...

    If you are creating the table you can set the start and increment at that moment though...

    IDENTITY

    Indicates that the new column is an identity column. When a new row is added to the table, Microsoft® SQL Server™ provides a unique, incremental value for the column. Identity columns are commonly used in conjunction with PRIMARY KEY constraints to serve as the unique row identifier for the table. The IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) columns. Only one identity column can be created per table. Bound defaults and DEFAULT constraints cannot be used with an identity column. You must specify both the seed and increment or neither. If neither is specified, the default is (1,1).

    seed

    Is the value used for the very first row loaded into the table.

    increment

    Is the incremental value added to the identity value of the previous row loaded.

    Example...

    CREATE TABLE Sales
    (SaleID INT IDENTITY(100000,1) NOT FOR REPLICATION,
    CHECK NOT FOR REPLICATION (SaleID <= 199999),
    SalesRegion CHAR(2),
    CONSTRAINT ID_PK PRIMARY KEY (SaleID)
    )


    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

Posting Permissions

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



Click Here to Expand Forum to Full Width