Autonumber as Primary Key

[mekohler]

Hi, I don't know if this is the correct forum or not. This is my situation: I use SSIS to bring in data from a separate SQL database to populate a table. It is during this process that I populate an ID field in the new table that is an integer, is auto number and acts as a primary key. Using SSIS I update the table every night. There is someone else in my office who then uses Access as a front end to create forms etc. to link to that table to populate other tables. She uses the ID field as a primary key in order to link to other tables; this ID field becomes development permit number. My question is when data is added or deleted in the original database, what happens to the auto numbering when data is added or deleted? Once an ID number has been assigned to a record, I don't want a different number assigned when I run the update module.
Basically I have a database in SQL for our GIS that brings in data from a proprietary database. I have created a database for development permits that combines financial data from the proprietary database with GIS data so that the data can be mapped.
Thanks,
Michael Kohler

 

[fredericofonseca]

by autonumber I am going to assume a identity field.

These will not be reused except on the following situations.

1 - table is truncated - number restarts
2 - table is reseeded - value starts at whatever value you use.
3 - you set identity insert on that table and manually add a record with a previously used value

If you delete a record its number is not reused except on the above conditions.

If on the other hand you are using a sequence (2012 or higher) then rules are different... buy hey that is what manuals are for!!


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[mekohler]

Hi thanks for the reply. The table is truncated; is there an alternative? I don't know anyway of updating the data in the table without dropping the old data. And yes, it's an identity field. If the table is populated once, is there a way of populating the id field without having it as an identity field? I'm using SQL 2012, is there a topic I can research that would help this project.
Cheers,
Michael

 

[fredericofonseca]

depending on the volumes you may delete instead of truncate. it is slower but depending on volume not necessarily an issue.

otherwise with 2012 you can go and read about sequences at

https://msdn.microsoft.com/en-us/library/ff878091(v=sql.110).aspx

you may also considering using a uniqueidentifier for the purpose of marking a record (and letting that be used by other systems).

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886