AutoNumber type in SQL Server

[maha12]

Hi all

I change Access 2000 db to SQLSqerver 2000. I have AutoNumber field(ID) in Access db and I want SQLSqerver to do the same thing (to have ID automatically like access does). How do I do.

Tks

Maha

 

[JayKusch]

open the tabe in Enterprise Manager. You do this by right clicking the table and choosing Design Table.

Once in design mode, click on the field you want to "Auto Icrement".

On the bottom of the design screen you will toggle the "Identity" drop down option from NO to YES.

Now for your "Identity Seed" we would set it to 1 for a new table with NO records in it yet. This would start the numbering from 1. NOW ... if you already have records in this table, you have to make sure ALL the records in the field are unique THEN find what the largest number is so far. Say it is 10429 ... We would then set the Indentity Seed value to 10430 so it will start counting/sequencing from that number.

Last item to tweak is the "Identity Increment". Usually that is set to 1 so we auto increment by 1 BUT you can put any number you want to increment by.

Once in place, save your table and you are done.

Make sure your export from Access to SQL Server has the the "Enable Identity Insert" checked in the Transformation section of the "Select Source Tables and Views". It is tweaked by clicking on the elipse (...) under the "Transform" section of the "Select Source Tables and Views" screen during the export wizard process.

Thanks

J. Kusch