Inserting an Autonumber field

[ScottishFencer]

Hi There,

I've been looking for a simple answer to a question I have. I'm not sure if this is the right forum and I apologise if not.

The question is quite simple and is this:

Is it possible to add an autonumber field to an existing table that already contains data?

FYI: I the SQL server is used as a backend to an Access front end.

I'm getting error messages that say this isn't possible.

I've thought this over and one way that I can think of to deal with this is to move the data out of the existing table, alter (now empty) tables tructure to include the new autonumber field, and then repopulate it.

Is this the only way?

 

[SQLDenis]

Sure you can, here is proof

create table testidnow (value datetime)

insert into testidnow
select getdate()

select * from testidnow

alter table testidnow add id int identity

select * from testidnow

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[ScottishFencer]

Hmmm... So I wonder why I was getting an error message then. I'll give it another try tomorrow and be in touch if there are more problems.

 

[jbenson001]

Also, post the exact error message is you still receive one.

jim

 

[SQLSister]

Are you trying to convert an existing field to an autonumber (identity field in SQL Server)? Exactly what code are you using to insert the Identity field?

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[SQLBill]

And just something else to check...what version of SQL Server are you using (including SP level)?

-SQLBill

Posting advice: FAQ481-4875

 

[ScottishFencer]

Cheers guys.

It worked today. I must have had a "blonde day" yesterday.

Thanks for your help.