How do I assign nos for column

I have existing data in a table and would like to
assign auto number to a column.
How can I do?
Thank you for your help in advance!
-KimTo add an identity column to your existing table you can issue the following
command:
alter table yourtable add newcol int identity
--
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"Kim" <anonymous@.discussions.microsoft.com> wrote in message
news:00af01c49045$a6f16e80$a401280a@.phx.gbl...
> I have existing data in a table and would like to
> assign auto number to a column.
> How can I do?
> Thank you for your help in advance!
> -Kim|||Greg,
I don't want to add a column, I already have a column,
I want to assign sequence numbers to this column.
Sequence numbers will depend on another field, if
the field value = 'A' it will have one sequence,
if the field value = 'B' then it will have another
sequence, so on...
here is the example -
Col1 Col2 Col3 ...
T123 Test rec1 A
F001 Test Rec2 B
S0001 Test Rec3 A
P001 Test Rec4 A
it should have the following values ...
Col1 Col2 Col3 ...
A00001 Test rec1 A
B00001 Test Rec2 B
A00002 Test Rec3 A
A00003 Test Rec4 A
Hope this helps!
Thank you,
-Kim
>--Original Message--
>To add an identity column to your existing table you can
issue the following
>command:
>alter table yourtable add newcol int identity
>--
>----
--
>----
--
>-
>Need SQL Server Examples check out my website
>http://www.geocities.com/sqlserverexamples
>
>"Kim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:00af01c49045$a6f16e80$a401280a@.phx.gbl...
>> I have existing data in a table and would like to
>> assign auto number to a column.
>> How can I do?
>> Thank you for your help in advance!
>> -Kim
>
>.
>|||Kim wrote:
> Greg,
> I don't want to add a column, I already have a column,
> I want to assign sequence numbers to this column.
> Sequence numbers will depend on another field, if
> the field value = 'A' it will have one sequence,
> if the field value = 'B' then it will have another
> sequence, so on...
> here is the example -
> Col1 Col2 Col3 ...
> T123 Test rec1 A
> F001 Test Rec2 B
> S0001 Test Rec3 A
> P001 Test Rec4 A
> it should have the following values ...
>
> Col1 Col2 Col3 ...
> A00001 Test rec1 A
> B00001 Test Rec2 B
> A00002 Test Rec3 A
> A00003 Test Rec4 A
> Hope this helps!
> Thank you,
> -Kim
If you know the number of possible col3 values beforehand, you can write
a T-SQL script to move through the table, grab each row, one at a time,
check the col3 value, increment the corresponding counter value in the
script, and update the row using the counter value.
Pseudo-Code Here:
Start all counters at 0
Loop through a cursor on the table (do this off-hours)
Get col3 value
If col3 = 'A' then
CounterA = CounterA + 1
NewCol1 = col3 + Right('0000' + CAST(CounterA as varchar(5)), 5)
If col3 = 'B' Then
CounterB = CounterB + 1
etc.
Update Table
Set Col1 = NewCol1
Where PKVal = WhateverThePKValueIs
--
David G.|||David,
I will implement your suggestion - thanks.
In the meantime wanted to find out from you how
can I create sequences for each of the series for future
use? Like for 'A' ... 'A000001' onwards,
for 'B' ... 'B000001' onwards.
'cause after I update my database with these numbers
I would like it to autogenerate while creating records
for each of the series.
Thank you for your help!
-Kim
>--Original Message--
>Kim wrote:
>> Greg,
>> I don't want to add a column, I already have a column,
>> I want to assign sequence numbers to this column.
>> Sequence numbers will depend on another field, if
>> the field value = 'A' it will have one sequence,
>> if the field value = 'B' then it will have another
>> sequence, so on...
>> here is the example -
>> Col1 Col2 Col3 ...
>> T123 Test rec1 A
>> F001 Test Rec2 B
>> S0001 Test Rec3 A
>> P001 Test Rec4 A
>> it should have the following values ...
>>
>> Col1 Col2 Col3 ...
>> A00001 Test rec1 A
>> B00001 Test Rec2 B
>> A00002 Test Rec3 A
>> A00003 Test Rec4 A
>> Hope this helps!
>> Thank you,
>> -Kim
>
>If you know the number of possible col3 values
beforehand, you can write
>a T-SQL script to move through the table, grab each row,
one at a time,
>check the col3 value, increment the corresponding
counter value in the
>script, and update the row using the counter value.
>Pseudo-Code Here:
>Start all counters at 0
>Loop through a cursor on the table (do this off-hours)
> Get col3 value
> If col3 = 'A' then
> CounterA = CounterA + 1
> NewCol1 = col3 + Right('0000' + CAST(CounterA as
varchar(5)), 5)
> If col3 = 'B' Then
> CounterB = CounterB + 1
> etc.
> Update Table
> Set Col1 = NewCol1
> Where PKVal = WhateverThePKValueIs
>--
>David G.
>.
>|||Kim wrote:
> David,
> I will implement your suggestion - thanks.
> In the meantime wanted to find out from you how
> can I create sequences for each of the series for future
> use? Like for 'A' ... 'A000001' onwards,
> for 'B' ... 'B000001' onwards.
> 'cause after I update my database with these numbers
> I would like it to autogenerate while creating records
> for each of the series.
> Thank you for your help!
> -Kim
>> --Original Message--
>> Kim wrote:
>> Greg,
>> I don't want to add a column, I already have a column,
>> I want to assign sequence numbers to this column.
>> Sequence numbers will depend on another field, if
>> the field value = 'A' it will have one sequence,
>> if the field value = 'B' then it will have another
>> sequence, so on...
>> here is the example -
>> Col1 Col2 Col3 ...
>> T123 Test rec1 A
>> F001 Test Rec2 B
>> S0001 Test Rec3 A
>> P001 Test Rec4 A
>> it should have the following values ...
>>
>> Col1 Col2 Col3 ...
>> A00001 Test rec1 A
>> B00001 Test Rec2 B
>> A00002 Test Rec3 A
>> A00003 Test Rec4 A
>> Hope this helps!
>> Thank you,
>> -Kim
>>
>> If you know the number of possible col3 values beforehand, you can
>> write a T-SQL script to move through the table, grab each row, one
>> at a time, check the col3 value, increment the corresponding counter
>> value in the script, and update the row using the counter value.
>> Pseudo-Code Here:
>> Start all counters at 0
>> Loop through a cursor on the table (do this off-hours)
>> Get col3 value
>> If col3 = 'A' then
>> CounterA = CounterA + 1
>> NewCol1 = col3 + Right('0000' + CAST(CounterA as varchar(5)), 5)
>> If col3 = 'B' Then
>> CounterB = CounterB + 1
>> etc.
>> Update Table
>> Set Col1 = NewCol1
>> Where PKVal = WhateverThePKValueIs
>> --
>> David G.
>> .
You can create a trigger on the table or a before trigger if the value
is not part of the PK. You'll have to keep track of the underlying key
values using another table.
I'm not a big fan of these types of intelligent keys because maintance
and implementation are much more difficult than using an indentity
columns. Have you considered using an identity column with the col3 as
the compound PK. That way, you have to do nothing to get the values in
there. You can then create a computed column on the table to return
values to your application in the correct format.
David G.