Loop through relation to set incremental number

I have a relation with two attributes acting as the primary key. Each key
is a foreign key to another table. The requirements have changed and now I
need to set the relation with a single auto increment primary key. The
existing relation has about 45k tuples.
As you might guess, if I add a new attribute with the increment on I get
errors because the new attribute can't contain NULL values. I have tried to
look at ways to loop through each tuple and set the new attribute equal to a
counter variable but the end result is the value is always one. (That is
the start of my counter).
Any thoughts and suggestions are greatly appreciated.
WBIf I understand your question properly...
Try the following...
Alter Table <TableName> Add <NewColumnName> Integer Identity Not Null
This should add a new column (Attribute) to the table (Relation) and
automatically populate it with non-null sequentially increasing unique
numeric values.
"WB" wrote:

> I have a relation with two attributes acting as the primary key. Each key
> is a foreign key to another table. The requirements have changed and now
I
> need to set the relation with a single auto increment primary key. The
> existing relation has about 45k tuples.
> As you might guess, if I add a new attribute with the increment on I get
> errors because the new attribute can't contain NULL values. I have tried
to
> look at ways to loop through each tuple and set the new attribute equal to
a
> counter variable but the end result is the value is always one. (That is
> the start of my counter).
> Any thoughts and suggestions are greatly appreciated.
> WB
>
>|||"WB" <none> wrote in message news:O1WVap0IFHA.3356@.TK2MSFTNGP12.phx.gbl...
>I have a relation with two attributes acting as the primary key. Each key
> is a foreign key to another table. The requirements have changed and now
> I
> need to set the relation with a single auto increment primary key. The
> existing relation has about 45k tuples.
> As you might guess, if I add a new attribute with the increment on I get
> errors because the new attribute can't contain NULL values. I have tried
> to
> look at ways to loop through each tuple and set the new attribute equal to
> a
> counter variable but the end result is the value is always one. (That is
> the start of my counter).
> Any thoughts and suggestions are greatly appreciated.
> WB
>
--Starting with
create table t(c1 int not null, c2 int not null, c3 varchar(255),
constraint pk_t primary key (c1,c2))
--create
create table t_new(id int identity primary key, c1 int, c2 int, c3
varchar(555),
constraint ak_t unique(c1,c2))
insert t_new(c1,c2,c3) select c1,c2,c3 from t
exec sp_rename t,t_old
exec sp_rename t_new,t
David|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:e5RcQ40IFHA.3628@.TK2MSFTNGP15.phx.gbl...
> insert t_new(c1,c2,c3) select c1,c2,c3 from t
Might want to add an ORDER BY clause to that one (if there's some
meaningful order?)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||I failed to mention that the database is part of a merge replication. Will
your suggestions work across merge replication?
"WB" <none> wrote in message news:O1WVap0IFHA.3356@.TK2MSFTNGP12.phx.gbl...
> I have a relation with two attributes acting as the primary key. Each key
> is a foreign key to another table. The requirements have changed and now
I
> need to set the relation with a single auto increment primary key. The
> existing relation has about 45k tuples.
> As you might guess, if I add a new attribute with the increment on I get
> errors because the new attribute can't contain NULL values. I have tried
to
> look at ways to loop through each tuple and set the new attribute equal to
a
> counter variable but the end result is the value is always one. (That is
> the start of my counter).
> Any thoughts and suggestions are greatly appreciated.
> WB
>sql