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
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
|||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...
>
>.
>
|||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:
>
>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:[vbcol=seagreen]
> 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
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.
Showing posts with label existing. Show all posts
Showing posts with label existing. Show all posts
Friday, March 30, 2012
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.
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.
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...
>
>.
>|||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:
>
>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:[vbcol=seagreen]
> 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
>
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.
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...
>
>.
>|||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:
>
>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:[vbcol=seagreen]
> 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
>
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.
Wednesday, March 28, 2012
How do I add a new column to an existing Data Source View in SSRS?
I've got a table in my Data Source View that is one of our central entity
tables in our warehouse, and there are LOTS of relationship lines (Roles)
linking to this table. We've just added 6 new columns to this table, and I
need to add them to the Data Source View so the new columns will be available
to the end users running Report Builder.
I am pulling my hair out trying to find the option to add the new columns!
Completely removing and re-adding the table is NOT an option, as we have 37
relationship lines coming into this central entity table.Hello here,
From your description, my understanding of this issue is that, you add some
new columns in the source table in database and you want to reflect in the
Data Source View. If I am offset, please feel free to let me know.
Based on my research, you could not add the new column in the data source
view directly. My suggestion is that you could regenerate the model. Since
the wizard will generate the relationship automatically, you will not
concern about creating many relationships.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi Wei Lu,
Thanks for your post. The wizard does not automatically re-establish all
the relationship lines. These 37 relationship lines I had to manually create
the very first time when I generated the model, even though most of them
already have a foreign key in the database expressing the relationship. I do
not want to have to manually re-create all these relationship lines. Also, I
have other computed expression columns that would be blown away if I
re-generate the entire Data Source View using the Wizard. I would have to
manually recreate those as well.
"Wei Lu [MSFT]" wrote:
> Hello here,
> From your description, my understanding of this issue is that, you add some
> new columns in the source table in database and you want to reflect in the
> Data Source View. If I am offset, please feel free to let me know.
> Based on my research, you could not add the new column in the data source
> view directly. My suggestion is that you could regenerate the model. Since
> the wizard will generate the relationship automatically, you will not
> concern about creating many relationships.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello,
I would like to suggest you use the Refresh button in the DSV designer,
then use the Generate option on the corresponding entity in the report
model.
Please let me know if this resolved your problem.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Perfect!! The Refresh button did the trick!! Thank you so much!
=Steve=
"Wei Lu [MSFT]" wrote:
> Hello,
> I would like to suggest you use the Refresh button in the DSV designer,
> then use the Generate option on the corresponding entity in the report
> model.
> Please let me know if this resolved your problem.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello,
Glad to hear that you resolve this issue. If you have any question, please
feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)
tables in our warehouse, and there are LOTS of relationship lines (Roles)
linking to this table. We've just added 6 new columns to this table, and I
need to add them to the Data Source View so the new columns will be available
to the end users running Report Builder.
I am pulling my hair out trying to find the option to add the new columns!
Completely removing and re-adding the table is NOT an option, as we have 37
relationship lines coming into this central entity table.Hello here,
From your description, my understanding of this issue is that, you add some
new columns in the source table in database and you want to reflect in the
Data Source View. If I am offset, please feel free to let me know.
Based on my research, you could not add the new column in the data source
view directly. My suggestion is that you could regenerate the model. Since
the wizard will generate the relationship automatically, you will not
concern about creating many relationships.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi Wei Lu,
Thanks for your post. The wizard does not automatically re-establish all
the relationship lines. These 37 relationship lines I had to manually create
the very first time when I generated the model, even though most of them
already have a foreign key in the database expressing the relationship. I do
not want to have to manually re-create all these relationship lines. Also, I
have other computed expression columns that would be blown away if I
re-generate the entire Data Source View using the Wizard. I would have to
manually recreate those as well.
"Wei Lu [MSFT]" wrote:
> Hello here,
> From your description, my understanding of this issue is that, you add some
> new columns in the source table in database and you want to reflect in the
> Data Source View. If I am offset, please feel free to let me know.
> Based on my research, you could not add the new column in the data source
> view directly. My suggestion is that you could regenerate the model. Since
> the wizard will generate the relationship automatically, you will not
> concern about creating many relationships.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello,
I would like to suggest you use the Refresh button in the DSV designer,
then use the Generate option on the corresponding entity in the report
model.
Please let me know if this resolved your problem.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Perfect!! The Refresh button did the trick!! Thank you so much!
=Steve=
"Wei Lu [MSFT]" wrote:
> Hello,
> I would like to suggest you use the Refresh button in the DSV designer,
> then use the Generate option on the corresponding entity in the report
> model.
> Please let me know if this resolved your problem.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello,
Glad to hear that you resolve this issue. If you have any question, please
feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)
How do I add a data source view and report model to an existing project
I have an existing project with the data source and report folder with working reports. I need to create a model for other department members to use Report Builder, not Business Intelligence Developer Studio, to develop their reports from. How do I add a data source view and report model folder to the project so I can then create a view and model?
Report Models are part of a separate project type. Create a new "Report Model" project and you will be able to create the DSV and report model there.
Wednesday, March 21, 2012
how check the existing index
Hello,
How can I check an existing index on a table?
I have found this:
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'my_ind_name')
DROP INDEX my_table_name.my_ind_name
My Problem is following:
1. What happens when this index is on an other table (e.g 'my_other_table')
and not on the original table ('my_table_name') Will I get an error
message? I would like to avoid this.
2. As far as I know it is not recommended to reffer to system objects
directly.
Instead of it I should use INFORMATION_SCHEMA. But how?
Thanks for any help
Danyi, AttilaTry,
if exists(
select
*
from
sysindexes
where
[id] = object_id('owner.table_name')
and [indid] between 1 and 254
and indexproperty([id], [name], 'IsStatistics') = 0
and indexproperty([id], [name], 'IsHypothetical') = 0
and [name] = 'index_name'
)
...
There is not an information_schema view to query about indexes.
AMB
"Danyi, Attila" wrote:
> Hello,
> How can I check an existing index on a table?
> I have found this:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'my_ind_name')
> DROP INDEX my_table_name.my_ind_name
> My Problem is following:
> 1. What happens when this index is on an other table (e.g 'my_other_table
')
> and not on the original table ('my_table_name') Will I get an error
> message? I would like to avoid this.
> 2. As far as I know it is not recommended to reffer to system objects
> directly.
> Instead of it I should use INFORMATION_SCHEMA. But how?
> Thanks for any help
> Danyi, Attila
>|||> 1. What happens when this index is on an other table (e.g
> 'my_other_table')
> and not on the original table ('my_table_name') Will I get an error
> message? I would like to avoid this.
You need to also filter on table:
IF EXISTS (SELECT * FROM sysindexes
WHERE name = 'my_ind_name' AND
OBJECT_NAME(id) = N'my_table_name')
DROP INDEX my_table_name.my_ind_name
> 2. As far as I know it is not recommended to reffer to system objects
> directly.
> Instead of it I should use INFORMATION_SCHEMA. But how?
Indexes are not exposed via the INFORMATION_SCHEMA views because these are
implementation specific. It's ok to use documented system table columns and
functions to query meta data, although I prefer to use the
INFORMATION_SCHEMA views when possible.
Hope this helps.
Dan Guzman
SQL Server MVP
"Danyi, Attila" <DanyiAttila@.discussions.microsoft.com> wrote in message
news:4241A175-9CA7-4B83-903D-E2787BE3927C@.microsoft.com...
> Hello,
> How can I check an existing index on a table?
> I have found this:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'my_ind_name')
> DROP INDEX my_table_name.my_ind_name
> My Problem is following:
> 1. What happens when this index is on an other table (e.g
> 'my_other_table')
> and not on the original table ('my_table_name') Will I get an error
> message? I would like to avoid this.
> 2. As far as I know it is not recommended to reffer to system objects
> directly.
> Instead of it I should use INFORMATION_SCHEMA. But how?
> Thanks for any help
> Danyi, Attila
>
How can I check an existing index on a table?
I have found this:
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'my_ind_name')
DROP INDEX my_table_name.my_ind_name
My Problem is following:
1. What happens when this index is on an other table (e.g 'my_other_table')
and not on the original table ('my_table_name') Will I get an error
message? I would like to avoid this.
2. As far as I know it is not recommended to reffer to system objects
directly.
Instead of it I should use INFORMATION_SCHEMA. But how?
Thanks for any help
Danyi, AttilaTry,
if exists(
select
*
from
sysindexes
where
[id] = object_id('owner.table_name')
and [indid] between 1 and 254
and indexproperty([id], [name], 'IsStatistics') = 0
and indexproperty([id], [name], 'IsHypothetical') = 0
and [name] = 'index_name'
)
...
There is not an information_schema view to query about indexes.
AMB
"Danyi, Attila" wrote:
> Hello,
> How can I check an existing index on a table?
> I have found this:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'my_ind_name')
> DROP INDEX my_table_name.my_ind_name
> My Problem is following:
> 1. What happens when this index is on an other table (e.g 'my_other_table
')
> and not on the original table ('my_table_name') Will I get an error
> message? I would like to avoid this.
> 2. As far as I know it is not recommended to reffer to system objects
> directly.
> Instead of it I should use INFORMATION_SCHEMA. But how?
> Thanks for any help
> Danyi, Attila
>|||> 1. What happens when this index is on an other table (e.g
> 'my_other_table')
> and not on the original table ('my_table_name') Will I get an error
> message? I would like to avoid this.
You need to also filter on table:
IF EXISTS (SELECT * FROM sysindexes
WHERE name = 'my_ind_name' AND
OBJECT_NAME(id) = N'my_table_name')
DROP INDEX my_table_name.my_ind_name
> 2. As far as I know it is not recommended to reffer to system objects
> directly.
> Instead of it I should use INFORMATION_SCHEMA. But how?
Indexes are not exposed via the INFORMATION_SCHEMA views because these are
implementation specific. It's ok to use documented system table columns and
functions to query meta data, although I prefer to use the
INFORMATION_SCHEMA views when possible.
Hope this helps.
Dan Guzman
SQL Server MVP
"Danyi, Attila" <DanyiAttila@.discussions.microsoft.com> wrote in message
news:4241A175-9CA7-4B83-903D-E2787BE3927C@.microsoft.com...
> Hello,
> How can I check an existing index on a table?
> I have found this:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'my_ind_name')
> DROP INDEX my_table_name.my_ind_name
> My Problem is following:
> 1. What happens when this index is on an other table (e.g
> 'my_other_table')
> and not on the original table ('my_table_name') Will I get an error
> message? I would like to avoid this.
> 2. As far as I know it is not recommended to reffer to system objects
> directly.
> Instead of it I should use INFORMATION_SCHEMA. But how?
> Thanks for any help
> Danyi, Attila
>
Subscribe to:
Posts (Atom)