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, PierreI'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).|||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,

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.

CREATE TABLE [dbo].[tbl_MyTable] (
[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

[i]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.|||Thank you very much,
that was the solution.

It does work fine.

Pierre.