Thank you for your help but the answer that i needed was this so i help it helps someone else.
alter table yourtable
add autonumber int identity(1,1)
yes that works, but it adds the column to the end of the table.
I've always tried to design tables so the identity is the first column in the table.
also, the column name of that identity is the same as the table itself, plus a suffix, ie the table City
has the first column as CityID
int identity(1,1) for example.
i find it makes it much easier and more intuitive when you have to write queries that are joining together, where if you know the table name, you can infer the identity by tablename + TBLKEY or ID or whatever is comfortable for you.
in your example, would every table have an "autonumber" column?
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!