Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    2

    Question Unanswered: Assign Sequential Numbers

    I am trying to automatically insert records into my existing customer table. Is there a way when I insert these new records and assign the customer number that it can sequentially pick the next available unique customer number for each record that is inserted? for example the first record would be customer number 100, the next 101, and so on? Please advise.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    An IDENTITY column is just what you need. Check the Microsoft SQL Server CREATE TABLE documentation.

    -PatP

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Hey...

    SQLTeam still down?

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99(Col1 int IDENTITY(100,1), Col2 varchar(25))
    GO
    
    INSERT INTO myTable99(Col2)
    SELECT 'Brett' UNION ALL
    SELECT 'Pat' UNION ALL
    SELECT 'Gary'
    GO
    
    SELECT * FROM myTable99
    GO
    
    DROP TABLE myTable99
    GO
    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.

  4. #4
    Join Date
    Mar 2004
    Location
    Virginia
    Posts
    1
    I agree with Pat. Here is an exerp from the Create Table subject in Books Online for SQL:

    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).

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by Brett Kaiser
    Hey...

    SQLTeam still down?
    Nah, at least I can see it from here.

    -PatP

Posting Permissions

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