Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Posts
    54

    Unanswered: Migration from Access : AutoNumber

    I have proceed to an Access Migration to SQL Server.

    The Access field type "autonumber" are now
    of type "int" in SQL Server.
    I do not see in SQL Server a way to tell him that it is
    an auto counter.
    Is this transparent ?
    Will SQL Server manage it alone ?

    Thanks, Pierre
    Pierre (Pl-Arts)

  2. #2
    Join Date
    Jan 2003
    Location
    Pittsburgh, PA
    Posts
    86

    Re: Migration from Access : AutoNumber

    I'm not sure of your exact question, but in SQL Server the equivalent of "Autonumber" is IDENTITY. If you want to count by one, then its IDENTITY (1,1).

  3. #3
    Join Date
    Jan 2003
    Posts
    54
    I do not see the field type "identity" in SQL Server ?

    My question was, in Access we have the type of field
    "automatic number" which is an auto incremental integer field.
    (often ID numbers)

    After migration, this one become a simple "int" field.

    So in my sql statement, I never fullfill this field through
    the "insert", because it is automatic in ACCESS.

    Now, under SQL Server, it bugs.
    So I need to tell to SQL Server that this is an atomatic incremental field.

    I do not see how to do that.

    Thanks,
    Pierre.
    Pierre (Pl-Arts)

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    Pierre,

    The IDENTITY property is not a data type, but an attribute that can be assigned to INT data types (and other data types as well). From EM, you can right-click on the table and go into Design. In the design, click on the field you wish to set to auto-increment. In the area beneath the listing of columns, you will see a list of attributes (Description, Default Value, Precision, Scale, Identity, Identity Seed, Indentity Increment, etc...).

    Click on the Identity attribute and set it equal to "Yes".

    Alternatively, you may use a script to create the table. Here is a generic script that you may use. Note, however, that you will have issues with trying to do it this way. Obviously, you cannot create a table with the same name over the existing table. Also, if you create a temporary table and push the data from your existing table into it, you will have to enable Identity Insert (see SQL BOL). However, I thought you should see the DDL for creating a table with an Identity Column so that you might better understand what SQL is doing.

    Code:
    CREATE TABLE [dbo].[tbl_MyTable] (
    	[ID] [int] IDENTITY (1, 1) NOT NULL ,
    	[MyColumn1] [varchar] (50)  NOT NULL ,
    	[MyColumn2] [varchar] (3000)  NULL ,
    	[MyColumn3] [varchar] (50)  NOT NULL 
    ) ON [PRIMARY]
    Also not the following:
    1. The syntax for IDENTITY is INDENTITY (Seed, Increment)
    2. Seed is a starting value. You may not want to start at 1
    3. Increment is a value by which the increment the identity. You may want to increment by a value other than 1.

    HTH,

    Hugh Scott

    See SQL BOL for more information on IDENTITY

    Originally posted by Plarde
    I do not see the field type "identity" in SQL Server ?

    My question was, in Access we have the type of field
    "automatic number" which is an auto incremental integer field.
    (often ID numbers)

    After migration, this one become a simple "int" field.

    So in my sql statement, I never fullfill this field through
    the "insert", because it is automatic in ACCESS.

    Now, under SQL Server, it bugs.
    So I need to tell to SQL Server that this is an atomatic incremental field.

    I do not see how to do that.

    Thanks,
    Pierre.

  5. #5
    Join Date
    Jan 2003
    Posts
    54
    Thank you very much,
    that was the solution.

    It does work fine.

    Pierre.
    Pierre (Pl-Arts)

Posting Permissions

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