adding identifier number to records

I have a table already created and need to add an autonumber/ unique identif
ier
field.
If the table already contains records can I just add the new autonumber fiel
d
and run a query to populate this field for all records with unique values?
how do I do this?
are their other ways of doing this ? and how?
thanks
ChrisChris,
When you add and identity column to your table, SQL Server automatically
populates the column. You can not update an identity column.
Example:
use northwind
go
create table t (
colA char(1) not null unique
)
go
insert into t values('a')
insert into t values('b')
insert into t values('c')
go
select * from t
go
alter table t
add colB int not null identity
go
select * from t
go
drop table t
go
AMB
"Chris" wrote:

> I have a table already created and need to add an autonumber/ unique ident
ifier
> field.
> If the table already contains records can I just add the new autonumber fi
eld
> and run a query to populate this field for all records with unique values?
> how do I do this?
> are their other ways of doing this ? and how?
> thanks
> Chris|||I added the unique identifier column using table design.
However when I queried the database the values were all null.
Also since there are a lot of records in the table and generating the
alpha-numeric values does take processing time, I noticed adding the column
did not require any processing, (i.e. no hour glass).
Also I would prefer to just use a big int as it will take up less space and
be faster.
How can I use @.@.identity to update each record? is this possible?
Gracias Alejandro,
Tu eres muy asusto. He vido muchas siertos y respuestas de usted en los
forums.
Perdone mi espanol, estoy apprendiendo.
Por favor, constesta en ingles :)
"Alejandro Mesa" wrote:
> Chris,
> When you add and identity column to your table, SQL Server automatically
> populates the column. You can not update an identity column.
> Example:
> use northwind
> go
> create table t (
> colA char(1) not null unique
> )
> go
> insert into t values('a')
> insert into t values('b')
> insert into t values('c')
> go
> select * from t
> go
> alter table t
> add colB int not null identity
> go
> select * from t
> go
> drop table t
> go
>
> AMB
>
> "Chris" wrote:
>|||As I told you, you can not update an identity column.
AMB
"Chris" wrote:
> I added the unique identifier column using table design.
> However when I queried the database the values were all null.
> Also since there are a lot of records in the table and generating the
> alpha-numeric values does take processing time, I noticed adding the colum
n
> did not require any processing, (i.e. no hour glass).
> Also I would prefer to just use a big int as it will take up less space an
d
> be faster.
> How can I use @.@.identity to update each record? is this possible?
> Gracias Alejandro,
> Tu eres muy asusto. He vido muchas siertos y respuestas de usted en los
> forums.
> Perdone mi espanol, estoy apprendiendo.
> Por favor, constesta en ingles :)
> "Alejandro Mesa" wrote:
>|||okay, so a big int field that has been filled with a value using @.@.identity
can not be edited?
"Alejandro Mesa" wrote:
> As I told you, you can not update an identity column.
>
> AMB
> "Chris" wrote:
>|||I need to create a table with unique primary key on only one field.
How does one create or load a table with unique values in a primary key
field if
you have not been been provided unique values.
I'm happy to use just a big int data type to hold my unique values
"Chris" wrote:

> I have a table already created and need to add an autonumber/ unique ident
ifier
> field.
> If the table already contains records can I just add the new autonumber fi
eld
> and run a query to populate this field for all records with unique values?
> how do I do this?
> are their other ways of doing this ? and how?
> thanks
> Chris|||Correct.
Example:
use northwind
go
create table t (
colA char(1) not null unique
)
go
insert into t values('a')
insert into t values('b')
insert into t values('c')
go
select * from t
go
alter table t
add colB int not null identity
go
select * from t
go
-- this will give an error
update t
set colB = 4
where colB = 2
go
drop table t
go
AMB
"Chris" wrote:
> okay, so a big int field that has been filled with a value using @.@.identit
y
> can not be edited?
>
> "Alejandro Mesa" wrote:
>

Adding an autonumber column after the fact?

I have a csv file of quotes. I need to IMPORT the quotes into my SQL Server database. I created the table with an autonumber identity column, but the IMPORT fails because I can't INSERT NULL into my identity column. There is no option to autonumber or anything when I click the TRANSFORM button on the IMPORT wizard. So, I have decided to allow NULLS on the ID column and add the numbering later. I'd rather not have to manually number it, is there a way to do it with a query? I treid just setting it to not allow NULLS and to auto-incrememnt, but that didn't work...I would have asked you to use temp table for the import and then do INSERT INTO a table with IDENTITY SET. Now you do ANSI SQL ALTER TABLE to SET IDENTITY on the table. Run a search of ALTER table in the BOL (books online). Hope this helps.|||Thank-you for your help :)|||I am glad I could help.