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 table. Show all posts
Showing posts with label table. 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.
How do I add Virtual table to Report Model (dsv)?
Hi,
problem: I got "Sale" table that include Employee ID & Manager ID.
I want to connect" Employee" table twice to "Sale"
once: Sale.EmployeeID=Employee.EmployeeID
second: Sale.ManagerID=Employee.EmployeeID
how do I create a virtual table for the second connection? can't I solve it in the model layer or need to add view in the database layer?
Thanks,
Assaf
You need to open DSV in Model Designer and add new relationship with Sale.ManagerID=Employee.EmployeeID (you should already have one relship for Sale.EmployeeID=Employee.EmployeeID).
Then open the model and regen the two involved entities or create two new roles manually.
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 1 to a Field?
I have a Table: Thread
There are 2 relevant fields: ThreadID and Counter (integer).
I want a Stored Procedure that will get a record based on ThreadID and will add 1 to the counter.
I know how to do this as 2 stored procedures (one to get the current value of the counter and a second procedure to write the new value).
There must be a better way to do it as one procedure though.
Any suggestions?
Thanks,
Chris
You can do this in a number of ways. get the number and add the 1 at the application layer. or even as simple as
SELECT counter+1
FROM Thread
WHERE Threadid = @.Threadid
|||The example you give will return a number 1 larger, but I also need to have that new number written into the data.
Can I do that with 1 stored procedure?
Chris
|||
Chris Messineo:
I want a Stored Procedure that will get a record based on ThreadID and will add 1 to the counter.
Thats what you wanted right?
|||I'm sorry if my question is confusing.
I need to get the new value, but I also need to set that value in the db. I believe your solution will just get me the new value.
Does that make sense?
Chris
|||do an UPDATE then. You can write a proc that will do the update and return the value. you can either return using an OUTPUT parameter or a SELECT statement.
How do I ... loop horizontal?
If I have the raw data dumped into a big table as following:
Date P R M E Date P R M E Date P R M E Date P R M E
1/1/90 1 2 3 4 1/1/90 2 3 4 5 1/1/90 3 4 5 6 1/1/90 4 5 6 7
...
1/1/05 1 2 3 4 1/1/05 2 3 4 5 1/1/05 3 4 5 6 1/1/05 4 5 6 7
And this table has a repeating block [D, P, R, M,E] 300 times. Is it possible to write a loop query/stored procedures/triggers (or whatever it is) to read each repeating block and stack them on top of each other to insert into another table which has the same structure as following?
Look like this?
Date P R M E
1/1/90 1 2 3 4
...
1/1/05 1 2 3 4
1/1/90 2 3 4 5
...
1/1/05 2 3 4 5
1/1/90 3 4 5 6
...
1/1/05 3 4 5 6
If there is a solution would you please elaborate, example?
Thank you for the help.
shiparsonsUsing Perl this would be relatively easy (basically a one line script).
Using pure SQL Server tools (BCP and Transact-SQL), it can be done, but it would be rather ugly.
I'd try to request the data in another format. It would be easier if it was provided in a "cleaner" format. If that isn't a choice, look at the tools that you've got to see what makes the most sense, then use that to fix the problem.
-PatP|||I know when I'm looped I'm usually horizontal :D|||you're not drunk if you can lie on the floor without holding on|||shiparsons, please do not post two threads with the same subject on the same forum. If you have additional information, just append it as a new post.
If the number of blocks is constant, you could do this as a single butt-ugly UNION statement.
Otherwise, it is dynamic SQL time for you.
I may have a dynamic SQL algorithm that would be pretty concise for you. I'll try it in the morning if nobody else posts it first...|||You can use a simple for loop in DOS/NT Shell language to parse the file and generate new output to a redirected file if you do not have access to perl or a tool such as MKS Toolkit
It may be a bit of of work since you have multiple rows on a single line.
Google "for loop" "DOS" and you should be able to get some assistance.|||Here is some code that selects N columns at a time from any table.
You can modify it to do inserts, if you would like.
declare @.ColumnString varchar(500)
declare @.ColumnCounter int
declare @.ColumnIncrement int
declare @.TableName varchar(500)
declare @.NumColumns int
set @.ColumnIncrement = 3 --Number of columns to return for each statement
set @.TableName = 'TableName' --Name of your target table
set @.ColumnCounter = 0
set @.NumColumns =
(select count(*)
from sysobjects
inner join syscolumns on sysobjects.id = syscolumns.id
where sysobjects.name = @.TableName)
while @.ColumnCounter < @.NumColumns
begin
set @.ColumnString = null
set @.ColumnCounter = @.ColumnCounter + @.ColumnIncrement
select @.ColumnString = isnull(@.ColumnString + ', ', '') + syscolumns.name
from sysobjects
inner join syscolumns on sysobjects.id = syscolumns.id
where sysobjects.name = @.TableName
and colid > @.ColumnCounter - @.ColumnIncrement
and colid <= @.ColumnCounter
order by colid
exec ('select ' + @.ColumnString + ' from ' + @.TableName)
end|||Thank you for the suggestion and assistance.
I am trying to avoid using other "Langauage/command" and use SQL instead. I belive there should be a way to achieve what I am trying to do within SQL. It may need multiple steps.
I appreciate any brainstorming and help.
shiparsons|||Did you try the code I posted?|||Blindman,
Thank you for the help. I guess while I was composing my respose you just posted the code. Anyway, I will try and definitely let you know.
Many thanks,
shiparsons|||Blindman,
I have tried the code... You are the best! It works exactly what I want!
I tried to modify the code to insert into a table but got error message:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Test1Date'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Test2Date'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Test3Date'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Test4Date'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Test5Date'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Test6Date'.
....
Here is the code I modified. Any suggestion? Thank you in advance for the help!!
declare @.ColumnString varchar(500)
declare @.ColumnCounter int
declare @.ColumnIncrement int
declare @.TableName varchar(500)
declare @.NumColumns int
declare @.Staging varchar(500)
set @.ColumnIncrement = 8
set @.TableName = 'TestTable'
set @.Staging= 'Staging'
set @.ColumnCounter = 0
set @.NumColumns =
(select count(*)
from sysobjects
inner join syscolumns on sysobjects.id = syscolumns.id
where sysobjects.name = @.TableName)
while @.ColumnCounter < @.NumColumns
begin
set @.ColumnString = null
set @.ColumnCounter = @.ColumnCounter + @.ColumnIncrement
select @.ColumnString = isnull(@.ColumnString + ', ', '') + syscolumns.name
from sysobjects
inner join syscolumns on sysobjects.id = syscolumns.id
where sysobjects.name = @.TableName
and colid > @.ColumnCounter - @.ColumnIncrement
and colid <= @.ColumnCounter
order by colid
exec ('insert into' + @.staging + 'select ' + @.ColumnString + ' from ' + @.TableName)
end|||Why are you inserting into your staging table? I though the idea was to transform data while populating production tables?
I think it is erroring out because your dynamic INSERT statement does not list which column(s) to insert into.
Copy this just before your EXEC statement and see what code it is trying to run:
'insert into' + @.staging + 'select ' + @.ColumnString + ' from ' + @.TableName
If your Query Analyzer is set to output Text results then you can just copy the output into another QA window and execute it directly to trace your error.
Error tracing is one of the challenges of dynamic SQL.|||I still need to do some manipulations before load into production table. Thats why I need to insert into staging table.
I tried the code right before EXCE statement and still got error message:
Server: Msg 170, Level 15, State 1, Line 30
Line 30: Incorrect syntax near 'insert into'.
Any idea?
Thanks|||Comment out the EXEC statement so that you don't throw the error anymore, and instead post the dynamic SQL statement that would be executed.|||I replaced Exec (..) with the command :
'insert into' + @.staging + 'select ' + @.ColumnString + ' from ' + @.TableName
And run the entire code. However, I am still getting the error:
Server: Msg 170, Level 15, State 1, Line 32
Line 32: Incorrect syntax near 'insert into'.
What part is incorrect? I could not figure it out.
Thanks for the help again.
shiparsons|||how 'bout:
Declare @.sql varchar(8000), @.staging sysname, @.ColumnString varchar(12), @.TableName sysname, @.debug bit
Select @.staging = '@.t1', @.TableName = '@.t2', @.ColumnString = 'c2', @.debug = 0
Select @.sql = 'Set NoCount On Declare @.t1 table(c1 int) Declare @.t2 table(c2 int)'
Select @.sql = @.sql + char(10) + 'Insert @.t2 (c2) Select 1 Union Select 2'
Select @.sql = @.sql + char(10) + 'insert ' + @.staging + ' select ' + @.ColumnString + ' from ' + @.TableName
Select @.sql = @.sql + char(10) + 'Select * From @.t1; Select * From @.t2'
If @.debug = 1 Select @.sql
Else Exec (@.sql)|||Blindman,
I think I know the problem is...
The entire code you posted which pulls out the results I want and in addition it stacks all the column headings as well. That's why when I insert into 'staging table' error msg shows up.
Here is the example of what I mean:
the results from running the code
date column1 column2... column8
data set...
date column10 column11...column16
data set...
...
I have defined datatype for each column of my staging table as (date datetime and all else is float). That is why 'insert into' caused error. Is there any a way to modify or skip the headers?
Any suggestions are welcome!
Thanks for the help!
shiparsons|||I hope you meant that you replaced it with
SELECT 'insert into' + @.staging + 'select ' + @.ColumnString + ' from ' + @.TableName
It should not give you an error...
...and yes, it is likely that the problem is a lack of specifed columns for your insert statement. If the column names are the same, you should be able to run something like this:
exec ('insert into' + @.staging + '(' + @.ColumnString + ') select ' + @.ColumnString + ' from ' + @.TableName)|||Blindman,
I think the code should be:
exec ('insert' + 'into' + @.staging + '(' + @.ColumnString + ') select ' + @.ColumnString + ' from ' + @.TableName)
And I am still getting the error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Test1Date'.
...
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Test32Date'.
Unfortunately, all columns names are different ( total 32 which is standard for each input file).
Any other ideas?
Thanks.
MaxA,
I like your code which is simpler, however, like the problem i have with the code Blindman posted. Your code will mix the column headings into the results as well. I need to insert the results into staging table which has each datatype defined as either datetime or float. Is there a way not pulling the column headers as a part of results?
Results from your code
c1
----
1
2
c2
----
1
2
expected results so that I can insert into staging table
c1
----
1
2
1
2
Any help is highly appreciated!
shiparsons|||"exec ('insert' + 'into' + @.staging + '(' + @.ColumnString + ') select ' + @.ColumnString + ' from ' + @.TableName)"?
No, there isn't any need to split 'insert into' into 'insert' + 'into', which won't work anyway because you ommited the space character, and so it concatenates to "insertinto". That is certain to throw a syntax error.
To get around the different column names, try creating a view(s) based upon either your staging table or your production table that aliases the column names so they are compatible. Then reference the view in your statements rather than the table itself.
But it you are trying to insert into your staging table and your staging table is the same poorly designed schema you mentioned in your first few posts, then I think you are screwed because you are going to have a tough time getting your INSERT statement to insert into columns 1-5 on one run, 6-10 on then next, and so on...|||The results you are seeking are unexpected.
Remove ; Select * From @.t2 from my example - it was put there to demonstrate the accuracy of the Select * From @.t1.|||Blindman,
Here is the right code to insert into a table, which works exactly what I want!
insert into tablename
exec ('select ' + @.ColumnString + ' from ' + @.TableName)
Many thanks to everyone who replied this thread!
shiparsons
Date P R M E Date P R M E Date P R M E Date P R M E
1/1/90 1 2 3 4 1/1/90 2 3 4 5 1/1/90 3 4 5 6 1/1/90 4 5 6 7
...
1/1/05 1 2 3 4 1/1/05 2 3 4 5 1/1/05 3 4 5 6 1/1/05 4 5 6 7
And this table has a repeating block [D, P, R, M,E] 300 times. Is it possible to write a loop query/stored procedures/triggers (or whatever it is) to read each repeating block and stack them on top of each other to insert into another table which has the same structure as following?
Look like this?
Date P R M E
1/1/90 1 2 3 4
...
1/1/05 1 2 3 4
1/1/90 2 3 4 5
...
1/1/05 2 3 4 5
1/1/90 3 4 5 6
...
1/1/05 3 4 5 6
If there is a solution would you please elaborate, example?
Thank you for the help.
shiparsonsUsing Perl this would be relatively easy (basically a one line script).
Using pure SQL Server tools (BCP and Transact-SQL), it can be done, but it would be rather ugly.
I'd try to request the data in another format. It would be easier if it was provided in a "cleaner" format. If that isn't a choice, look at the tools that you've got to see what makes the most sense, then use that to fix the problem.
-PatP|||I know when I'm looped I'm usually horizontal :D|||you're not drunk if you can lie on the floor without holding on|||shiparsons, please do not post two threads with the same subject on the same forum. If you have additional information, just append it as a new post.
If the number of blocks is constant, you could do this as a single butt-ugly UNION statement.
Otherwise, it is dynamic SQL time for you.
I may have a dynamic SQL algorithm that would be pretty concise for you. I'll try it in the morning if nobody else posts it first...|||You can use a simple for loop in DOS/NT Shell language to parse the file and generate new output to a redirected file if you do not have access to perl or a tool such as MKS Toolkit
It may be a bit of of work since you have multiple rows on a single line.
Google "for loop" "DOS" and you should be able to get some assistance.|||Here is some code that selects N columns at a time from any table.
You can modify it to do inserts, if you would like.
declare @.ColumnString varchar(500)
declare @.ColumnCounter int
declare @.ColumnIncrement int
declare @.TableName varchar(500)
declare @.NumColumns int
set @.ColumnIncrement = 3 --Number of columns to return for each statement
set @.TableName = 'TableName' --Name of your target table
set @.ColumnCounter = 0
set @.NumColumns =
(select count(*)
from sysobjects
inner join syscolumns on sysobjects.id = syscolumns.id
where sysobjects.name = @.TableName)
while @.ColumnCounter < @.NumColumns
begin
set @.ColumnString = null
set @.ColumnCounter = @.ColumnCounter + @.ColumnIncrement
select @.ColumnString = isnull(@.ColumnString + ', ', '') + syscolumns.name
from sysobjects
inner join syscolumns on sysobjects.id = syscolumns.id
where sysobjects.name = @.TableName
and colid > @.ColumnCounter - @.ColumnIncrement
and colid <= @.ColumnCounter
order by colid
exec ('select ' + @.ColumnString + ' from ' + @.TableName)
end|||Thank you for the suggestion and assistance.
I am trying to avoid using other "Langauage/command" and use SQL instead. I belive there should be a way to achieve what I am trying to do within SQL. It may need multiple steps.
I appreciate any brainstorming and help.
shiparsons|||Did you try the code I posted?|||Blindman,
Thank you for the help. I guess while I was composing my respose you just posted the code. Anyway, I will try and definitely let you know.
Many thanks,
shiparsons|||Blindman,
I have tried the code... You are the best! It works exactly what I want!
I tried to modify the code to insert into a table but got error message:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Test1Date'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Test2Date'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Test3Date'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Test4Date'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Test5Date'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Test6Date'.
....
Here is the code I modified. Any suggestion? Thank you in advance for the help!!
declare @.ColumnString varchar(500)
declare @.ColumnCounter int
declare @.ColumnIncrement int
declare @.TableName varchar(500)
declare @.NumColumns int
declare @.Staging varchar(500)
set @.ColumnIncrement = 8
set @.TableName = 'TestTable'
set @.Staging= 'Staging'
set @.ColumnCounter = 0
set @.NumColumns =
(select count(*)
from sysobjects
inner join syscolumns on sysobjects.id = syscolumns.id
where sysobjects.name = @.TableName)
while @.ColumnCounter < @.NumColumns
begin
set @.ColumnString = null
set @.ColumnCounter = @.ColumnCounter + @.ColumnIncrement
select @.ColumnString = isnull(@.ColumnString + ', ', '') + syscolumns.name
from sysobjects
inner join syscolumns on sysobjects.id = syscolumns.id
where sysobjects.name = @.TableName
and colid > @.ColumnCounter - @.ColumnIncrement
and colid <= @.ColumnCounter
order by colid
exec ('insert into' + @.staging + 'select ' + @.ColumnString + ' from ' + @.TableName)
end|||Why are you inserting into your staging table? I though the idea was to transform data while populating production tables?
I think it is erroring out because your dynamic INSERT statement does not list which column(s) to insert into.
Copy this just before your EXEC statement and see what code it is trying to run:
'insert into' + @.staging + 'select ' + @.ColumnString + ' from ' + @.TableName
If your Query Analyzer is set to output Text results then you can just copy the output into another QA window and execute it directly to trace your error.
Error tracing is one of the challenges of dynamic SQL.|||I still need to do some manipulations before load into production table. Thats why I need to insert into staging table.
I tried the code right before EXCE statement and still got error message:
Server: Msg 170, Level 15, State 1, Line 30
Line 30: Incorrect syntax near 'insert into'.
Any idea?
Thanks|||Comment out the EXEC statement so that you don't throw the error anymore, and instead post the dynamic SQL statement that would be executed.|||I replaced Exec (..) with the command :
'insert into' + @.staging + 'select ' + @.ColumnString + ' from ' + @.TableName
And run the entire code. However, I am still getting the error:
Server: Msg 170, Level 15, State 1, Line 32
Line 32: Incorrect syntax near 'insert into'.
What part is incorrect? I could not figure it out.
Thanks for the help again.
shiparsons|||how 'bout:
Declare @.sql varchar(8000), @.staging sysname, @.ColumnString varchar(12), @.TableName sysname, @.debug bit
Select @.staging = '@.t1', @.TableName = '@.t2', @.ColumnString = 'c2', @.debug = 0
Select @.sql = 'Set NoCount On Declare @.t1 table(c1 int) Declare @.t2 table(c2 int)'
Select @.sql = @.sql + char(10) + 'Insert @.t2 (c2) Select 1 Union Select 2'
Select @.sql = @.sql + char(10) + 'insert ' + @.staging + ' select ' + @.ColumnString + ' from ' + @.TableName
Select @.sql = @.sql + char(10) + 'Select * From @.t1; Select * From @.t2'
If @.debug = 1 Select @.sql
Else Exec (@.sql)|||Blindman,
I think I know the problem is...
The entire code you posted which pulls out the results I want and in addition it stacks all the column headings as well. That's why when I insert into 'staging table' error msg shows up.
Here is the example of what I mean:
the results from running the code
date column1 column2... column8
data set...
date column10 column11...column16
data set...
...
I have defined datatype for each column of my staging table as (date datetime and all else is float). That is why 'insert into' caused error. Is there any a way to modify or skip the headers?
Any suggestions are welcome!
Thanks for the help!
shiparsons|||I hope you meant that you replaced it with
SELECT 'insert into' + @.staging + 'select ' + @.ColumnString + ' from ' + @.TableName
It should not give you an error...
...and yes, it is likely that the problem is a lack of specifed columns for your insert statement. If the column names are the same, you should be able to run something like this:
exec ('insert into' + @.staging + '(' + @.ColumnString + ') select ' + @.ColumnString + ' from ' + @.TableName)|||Blindman,
I think the code should be:
exec ('insert' + 'into' + @.staging + '(' + @.ColumnString + ') select ' + @.ColumnString + ' from ' + @.TableName)
And I am still getting the error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Test1Date'.
...
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Test32Date'.
Unfortunately, all columns names are different ( total 32 which is standard for each input file).
Any other ideas?
Thanks.
MaxA,
I like your code which is simpler, however, like the problem i have with the code Blindman posted. Your code will mix the column headings into the results as well. I need to insert the results into staging table which has each datatype defined as either datetime or float. Is there a way not pulling the column headers as a part of results?
Results from your code
c1
----
1
2
c2
----
1
2
expected results so that I can insert into staging table
c1
----
1
2
1
2
Any help is highly appreciated!
shiparsons|||"exec ('insert' + 'into' + @.staging + '(' + @.ColumnString + ') select ' + @.ColumnString + ' from ' + @.TableName)"?
No, there isn't any need to split 'insert into' into 'insert' + 'into', which won't work anyway because you ommited the space character, and so it concatenates to "insertinto". That is certain to throw a syntax error.
To get around the different column names, try creating a view(s) based upon either your staging table or your production table that aliases the column names so they are compatible. Then reference the view in your statements rather than the table itself.
But it you are trying to insert into your staging table and your staging table is the same poorly designed schema you mentioned in your first few posts, then I think you are screwed because you are going to have a tough time getting your INSERT statement to insert into columns 1-5 on one run, 6-10 on then next, and so on...|||The results you are seeking are unexpected.
Remove ; Select * From @.t2 from my example - it was put there to demonstrate the accuracy of the Select * From @.t1.|||Blindman,
Here is the right code to insert into a table, which works exactly what I want!
insert into tablename
exec ('select ' + @.ColumnString + ' from ' + @.TableName)
Many thanks to everyone who replied this thread!
shiparsons
How do I "suspend" merge replication while I extract data from central table?
Is there a standard practice to "hold" Merge replication so that the
data collected at the central server (in a remote sales order
application) can be periodically 'removed' to another part of the
system without clashing with ongoing replication from clients?
I'm experiencing my clients initiating replication, and therefore
inserting more rows, whilst the server SSIS job transfers and deletes
rows collected in the central table to the order processing tables,
thus losing data.
My implementation is SQL 2005 Express clients using Merge Websync to a
SQL 2005 standard central server.
Any advice/recommendations would be appreciated!
the best approach is to drop the subscriptions and publications and make the
changes. You can try to disable the triggers make changes, and then
re-enable them. However the results may be unpredictable.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<reefbreakbda@.hotmail.com> wrote in message
news:1175416990.053051.174730@.y80g2000hsf.googlegr oups.com...
> Is there a standard practice to "hold" Merge replication so that the
> data collected at the central server (in a remote sales order
> application) can be periodically 'removed' to another part of the
> system without clashing with ongoing replication from clients?
> I'm experiencing my clients initiating replication, and therefore
> inserting more rows, whilst the server SSIS job transfers and deletes
> rows collected in the central table to the order processing tables,
> thus losing data.
> My implementation is SQL 2005 Express clients using Merge Websync to a
> SQL 2005 standard central server.
> Any advice/recommendations would be appreciated!
>
data collected at the central server (in a remote sales order
application) can be periodically 'removed' to another part of the
system without clashing with ongoing replication from clients?
I'm experiencing my clients initiating replication, and therefore
inserting more rows, whilst the server SSIS job transfers and deletes
rows collected in the central table to the order processing tables,
thus losing data.
My implementation is SQL 2005 Express clients using Merge Websync to a
SQL 2005 standard central server.
Any advice/recommendations would be appreciated!
the best approach is to drop the subscriptions and publications and make the
changes. You can try to disable the triggers make changes, and then
re-enable them. However the results may be unpredictable.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<reefbreakbda@.hotmail.com> wrote in message
news:1175416990.053051.174730@.y80g2000hsf.googlegr oups.com...
> Is there a standard practice to "hold" Merge replication so that the
> data collected at the central server (in a remote sales order
> application) can be periodically 'removed' to another part of the
> system without clashing with ongoing replication from clients?
> I'm experiencing my clients initiating replication, and therefore
> inserting more rows, whilst the server SSIS job transfers and deletes
> rows collected in the central table to the order processing tables,
> thus losing data.
> My implementation is SQL 2005 Express clients using Merge Websync to a
> SQL 2005 standard central server.
> Any advice/recommendations would be appreciated!
>
How do i "Join" back to the same table and fields ? Please help !
Hi and thanx for reading my post..
Underneat is the current SQL i'm trying to get to work (in Access 2000)
Cant get it to work, cause i'm getting Join errors..
----CODE-----
strSQl = "SELECT p.PID, p.Lastname, p1.PID, p1.Lastname" & _
" FROM Person p, Person p1" & _
" INNER JOIN (PersonPerson pp ON pp.PID = p.PID OR pp.PID2 = p.PID)" & _
" WHERE (pp.PID LIKE '*" & CStr(Me.txtSearch) & "*'" & _
" OR pp.PID2 LIKE '*" & CStr(Me.txtSearch) & "*')"
----END CODE----
----DESCRIPTION-------
1) Got a table "Person" where i'm inserting PID and Lastname.
2) Registering person 1-> PID = 1, Lastname = Doe
3) Registering person 2-> PID = 2, Lastname = John
4) Using table PersonPerson (manually) to connect these two together.
PID1 = 1, PID2 = 2
5) By using this i can use SQL to get the data from both persons taht are
connected
6) So basically, i want to go through all records in the personperson table, to find PID and PID2, and then use _both_ the fields to check against the Person-table. First getting Person.PID and Person.Lastname (For PERSON 1) THEN... do the exact same operation for PID2.
----END DESCRIPTION------
Result should look like this :
Person1 ID, Person1 Lastname, Person2 ID, Person2 Lastname
------------------
1 Doe 2 John
Will be enormously happy if anyone could help me out with this code...
/(_Mirador:(I'm not 100% on what you want. Is this your table structure?
Person
PID Firstname etc
-- ---
1 Joel
2 Bell
3 Bruce
4 Harry
PersonPerson
PID1 PID2
-- --
1 2
4 3
If so - this should help:
strSQl = "SELECT p.PID, p.Lastname, p1.PID, p1.Lastname
FROM Person p, Person p1, PersonPerson pp
WHERE (p.PID = pp.PID1 AND p1.PID = pp.PID2)
AND (pp.PID LIKE '*" & CStr(Me.txtSearch) & "*'" & _
" OR pp.PID2 LIKE '*" & CStr(Me.txtSearch) & "*')"
It will generate the following output:
PID Lastname PID Lastname
---- ---- ---- ----
1 Dixon 2 Crawford
4 Potter 3 Shark
(Yes, Person 4's name is Harry Potter - but no - I haven't read the books :) )|||Great ! :)
I will try the code right away..
I'll send you the results i got :)
Mirador.|||Make sure you're using the latest code - I've edited the post a few times since I first posted it (stupid syntax bug)|||Super-great !!!!! :)
It worked like a dream..
You have no clue how much you've helped me !
Owe u a digital-beer.
Mirador.|||lol - no worries mate.
But I've gotta drive tonight - so how about a digital soda?|||/Me hands over a digital master-soda :)
hehe..
btw : since you're so master'ish at SQL.. maybe u could try and help me with this query too ?
----CODE-----
strSQl = "SELECT Person.PID" & _
" FROM Person INNER JOIN (Kjrety INNER JOIN PersonKjrety ON Kjrety.KID = PersonKjrety.KID) ON Person.PID = PersonKjrety.PID" & _
" WHERE Person.Etternavn LIKE '%" & Me.txtSearch & "%' OR Person.Etternavn LIKE '%" & Me.txtSearch2 & "%'" & _
" OR Person.Alias LIKE '%" & Me.txtSearch & "%' OR Person.Alias LIKE '%" & Me.txtSearch2 & "%' OR Person.Yrke LIKE '%" & Me.txtSearch & "%' OR Person.Yrke LIKE '%" & Me.txtSearch2 & "%'" & _ etc.etc.etc...
---- END CODE -----
---- DESCRIPTION-------
As you can see it's 2 x fields where i want to search..
Basically... i'm running 2 different queries depending on (and checking) if only one, or two fields are filled in for the search... AND...
If both the fields are filled in, it should ex : if Me.txtsearch is "June" and Me.txtSearch2 is "2004" make sure it's only getting a record if both "June" and "2004" is included in _ANY_ of the fields in the record..
I have realized that i cannot only use AND alone, because then all the fields have to match, and i cannot use OR alone either.. because then it grabs if it only matches one of them..
Got any clues on this one ?
Thanx for your help mate..
Mirador.|||So basically - you are trying to select records in which BOTH of the criteria is in ONE of the fields. If this is correct (and if it's not I'm lost :) ) - then this should help (take note of the brackets):
strSQl = "SELECT Person.PID" & _
" FROM Person INNER JOIN (Kjrety INNER JOIN PersonKjrety ON Kjrety.KID = PersonKjrety.KID) ON Person.PID = PersonKjrety.PID" & _
" WHERE (Person.Etternavn LIKE '%" & Me.txtSearch & "%' AND Person.Etternavn LIKE '%" & Me.txtSearch2 & "%'" & _
") OR (Person.Alias LIKE '%" & Me.txtSearch & "%' AND Person.Alias LIKE '%" & Me.txtSearch2 & "%') OR Person.Yrke LIKE '%" & Me.txtSearch & "%' AND Person.Yrke LIKE '%" & Me.txtSearch2 & "%')" & _ etc.etc.etc...
Basically you are saying (i'll use a generic example of a Product record. No offence - but I really can't understand the table you have with the column names ;) ):
If txtSearch1 is "big" and txtSearch2 is "round"
SELECT blah
FROM Product
WHERE (Product.Name LIKE '%big%' AND Product.Name LIKE '%round%')
OR (Product.Description LIKE '%big%' AND Product.Description LIKE '%round%')
OR (Product.Comment LIKE '%big%' AND Product.Comment LIKE '%round%')
So you will get products with big AND round in either the Name, Description or Comment column.
Is this what you were after - or have I just spoken pure gibberish?|||Hay mate :) thanx for your reply :)
did u enjoy your digital soda ? hehe.. :)
Well... gotta admit that it's a bit confusion for myself too :)
I'll try to explain a bit better :
---------
Q :
Lets say u want to search for every record that includes both "Green" and "Yellow" in _any_ of the fields in the record, and u want to see _only_ those records.
A:
You find a record where field "lastname" has "Green" in it and field "Comment" has "Yellow" in it -> MATCH!!
----------
If i understood it right, the one you posted has to have both "Green" and "Yellow" in one field right ?
So.. u would get a match if ex. "Comment" field had the text :
"All green people are infact Yellow because they bla-bla.-bla..."
I have to lets say.. use the second searchfield (txtsearch2) to... "narrow" down the search.
Example :
-------
Lets say u want to find a person named "Mike" and u press search. U would maybe get something like 1000 matches if u got a huuge database.
But then u altso know that "Mike" is from "Uganda". So... therefore i type "Mike" in txtsearch and "Uganda" in txtsearch2 to make sure that u get all the "Mike" records which got "Uganda" in any of the other fields..
-------
Yea.. that's about it :) dunno if it's even possible but i surely hope so : )heeh..
Hope that made things a littlebit more clear :)
Thanx for your help btw !!!.. most appreciated..
want another digital soda ? or.. maybe a digital beer this time :)
Best regards
Mirador.|||Ahh - I see what you mean. It's also possible - but the code is quite long, depending on the columns in your table. It's basically the same as my other example above - but switch the ANDs and ORs.
For each search criteria you have to check if it's in any of the columns. Again - using my Product table:
SELECT blah
FROM Product
WHERE (Product.Name LIKE <SearchCriteria1> OR Product.Description LIKE <SearchCriteria1> OR Product.Comment LIKE <SearchCriteria1>)
AND (Product.Name LIKE <SearchCriteria2> OR Product.Description LIKE <SearchCriteria2> OR Product.Comment LIKE <SearchCriteria2>)
Doing this for each column should get what you're after. Basically you're saying SearchCriteria1 needs to be in any of the columns (using the OR) - AND SearchCriteria2 needs to be in any of the columns.
Is that what you're after?
And I'm well past a digital beer - with the last few weeks I've had at work. Better make it a digital (double) scotch! :D|||Tjohooo !!..
yea.. that's JUST what i was after..
I had thoughts in this track, but wasn't certain because it would be so extremely long:) hehe.. Didn't know quite how to write it either..
but.. THANK YOU AGAIN !! :)
Double scotch coming up.. or.. maybe it's back to coffee ? heheh !!:
Mirador..|||And I'm well past a digital beer - with the last few weeks I've had at work. Better make it a digital (double) scotch! :DThese last few weeks (since mid-April) have been awful for me too. Do you suppose that the universe has taken some kind of unusually perverse twist against us "denizens of databases" lately?
-PatP
Underneat is the current SQL i'm trying to get to work (in Access 2000)
Cant get it to work, cause i'm getting Join errors..
----CODE-----
strSQl = "SELECT p.PID, p.Lastname, p1.PID, p1.Lastname" & _
" FROM Person p, Person p1" & _
" INNER JOIN (PersonPerson pp ON pp.PID = p.PID OR pp.PID2 = p.PID)" & _
" WHERE (pp.PID LIKE '*" & CStr(Me.txtSearch) & "*'" & _
" OR pp.PID2 LIKE '*" & CStr(Me.txtSearch) & "*')"
----END CODE----
----DESCRIPTION-------
1) Got a table "Person" where i'm inserting PID and Lastname.
2) Registering person 1-> PID = 1, Lastname = Doe
3) Registering person 2-> PID = 2, Lastname = John
4) Using table PersonPerson (manually) to connect these two together.
PID1 = 1, PID2 = 2
5) By using this i can use SQL to get the data from both persons taht are
connected
6) So basically, i want to go through all records in the personperson table, to find PID and PID2, and then use _both_ the fields to check against the Person-table. First getting Person.PID and Person.Lastname (For PERSON 1) THEN... do the exact same operation for PID2.
----END DESCRIPTION------
Result should look like this :
Person1 ID, Person1 Lastname, Person2 ID, Person2 Lastname
------------------
1 Doe 2 John
Will be enormously happy if anyone could help me out with this code...
/(_Mirador:(I'm not 100% on what you want. Is this your table structure?
Person
PID Firstname etc
-- ---
1 Joel
2 Bell
3 Bruce
4 Harry
PersonPerson
PID1 PID2
-- --
1 2
4 3
If so - this should help:
strSQl = "SELECT p.PID, p.Lastname, p1.PID, p1.Lastname
FROM Person p, Person p1, PersonPerson pp
WHERE (p.PID = pp.PID1 AND p1.PID = pp.PID2)
AND (pp.PID LIKE '*" & CStr(Me.txtSearch) & "*'" & _
" OR pp.PID2 LIKE '*" & CStr(Me.txtSearch) & "*')"
It will generate the following output:
PID Lastname PID Lastname
---- ---- ---- ----
1 Dixon 2 Crawford
4 Potter 3 Shark
(Yes, Person 4's name is Harry Potter - but no - I haven't read the books :) )|||Great ! :)
I will try the code right away..
I'll send you the results i got :)
Mirador.|||Make sure you're using the latest code - I've edited the post a few times since I first posted it (stupid syntax bug)|||Super-great !!!!! :)
It worked like a dream..
You have no clue how much you've helped me !
Owe u a digital-beer.
Mirador.|||lol - no worries mate.
But I've gotta drive tonight - so how about a digital soda?|||/Me hands over a digital master-soda :)
hehe..
btw : since you're so master'ish at SQL.. maybe u could try and help me with this query too ?
----CODE-----
strSQl = "SELECT Person.PID" & _
" FROM Person INNER JOIN (Kjrety INNER JOIN PersonKjrety ON Kjrety.KID = PersonKjrety.KID) ON Person.PID = PersonKjrety.PID" & _
" WHERE Person.Etternavn LIKE '%" & Me.txtSearch & "%' OR Person.Etternavn LIKE '%" & Me.txtSearch2 & "%'" & _
" OR Person.Alias LIKE '%" & Me.txtSearch & "%' OR Person.Alias LIKE '%" & Me.txtSearch2 & "%' OR Person.Yrke LIKE '%" & Me.txtSearch & "%' OR Person.Yrke LIKE '%" & Me.txtSearch2 & "%'" & _ etc.etc.etc...
---- END CODE -----
---- DESCRIPTION-------
As you can see it's 2 x fields where i want to search..
Basically... i'm running 2 different queries depending on (and checking) if only one, or two fields are filled in for the search... AND...
If both the fields are filled in, it should ex : if Me.txtsearch is "June" and Me.txtSearch2 is "2004" make sure it's only getting a record if both "June" and "2004" is included in _ANY_ of the fields in the record..
I have realized that i cannot only use AND alone, because then all the fields have to match, and i cannot use OR alone either.. because then it grabs if it only matches one of them..
Got any clues on this one ?
Thanx for your help mate..
Mirador.|||So basically - you are trying to select records in which BOTH of the criteria is in ONE of the fields. If this is correct (and if it's not I'm lost :) ) - then this should help (take note of the brackets):
strSQl = "SELECT Person.PID" & _
" FROM Person INNER JOIN (Kjrety INNER JOIN PersonKjrety ON Kjrety.KID = PersonKjrety.KID) ON Person.PID = PersonKjrety.PID" & _
" WHERE (Person.Etternavn LIKE '%" & Me.txtSearch & "%' AND Person.Etternavn LIKE '%" & Me.txtSearch2 & "%'" & _
") OR (Person.Alias LIKE '%" & Me.txtSearch & "%' AND Person.Alias LIKE '%" & Me.txtSearch2 & "%') OR Person.Yrke LIKE '%" & Me.txtSearch & "%' AND Person.Yrke LIKE '%" & Me.txtSearch2 & "%')" & _ etc.etc.etc...
Basically you are saying (i'll use a generic example of a Product record. No offence - but I really can't understand the table you have with the column names ;) ):
If txtSearch1 is "big" and txtSearch2 is "round"
SELECT blah
FROM Product
WHERE (Product.Name LIKE '%big%' AND Product.Name LIKE '%round%')
OR (Product.Description LIKE '%big%' AND Product.Description LIKE '%round%')
OR (Product.Comment LIKE '%big%' AND Product.Comment LIKE '%round%')
So you will get products with big AND round in either the Name, Description or Comment column.
Is this what you were after - or have I just spoken pure gibberish?|||Hay mate :) thanx for your reply :)
did u enjoy your digital soda ? hehe.. :)
Well... gotta admit that it's a bit confusion for myself too :)
I'll try to explain a bit better :
---------
Q :
Lets say u want to search for every record that includes both "Green" and "Yellow" in _any_ of the fields in the record, and u want to see _only_ those records.
A:
You find a record where field "lastname" has "Green" in it and field "Comment" has "Yellow" in it -> MATCH!!
----------
If i understood it right, the one you posted has to have both "Green" and "Yellow" in one field right ?
So.. u would get a match if ex. "Comment" field had the text :
"All green people are infact Yellow because they bla-bla.-bla..."
I have to lets say.. use the second searchfield (txtsearch2) to... "narrow" down the search.
Example :
-------
Lets say u want to find a person named "Mike" and u press search. U would maybe get something like 1000 matches if u got a huuge database.
But then u altso know that "Mike" is from "Uganda". So... therefore i type "Mike" in txtsearch and "Uganda" in txtsearch2 to make sure that u get all the "Mike" records which got "Uganda" in any of the other fields..
-------
Yea.. that's about it :) dunno if it's even possible but i surely hope so : )heeh..
Hope that made things a littlebit more clear :)
Thanx for your help btw !!!.. most appreciated..
want another digital soda ? or.. maybe a digital beer this time :)
Best regards
Mirador.|||Ahh - I see what you mean. It's also possible - but the code is quite long, depending on the columns in your table. It's basically the same as my other example above - but switch the ANDs and ORs.
For each search criteria you have to check if it's in any of the columns. Again - using my Product table:
SELECT blah
FROM Product
WHERE (Product.Name LIKE <SearchCriteria1> OR Product.Description LIKE <SearchCriteria1> OR Product.Comment LIKE <SearchCriteria1>)
AND (Product.Name LIKE <SearchCriteria2> OR Product.Description LIKE <SearchCriteria2> OR Product.Comment LIKE <SearchCriteria2>)
Doing this for each column should get what you're after. Basically you're saying SearchCriteria1 needs to be in any of the columns (using the OR) - AND SearchCriteria2 needs to be in any of the columns.
Is that what you're after?
And I'm well past a digital beer - with the last few weeks I've had at work. Better make it a digital (double) scotch! :D|||Tjohooo !!..
yea.. that's JUST what i was after..
I had thoughts in this track, but wasn't certain because it would be so extremely long:) hehe.. Didn't know quite how to write it either..
but.. THANK YOU AGAIN !! :)
Double scotch coming up.. or.. maybe it's back to coffee ? heheh !!:
Mirador..|||And I'm well past a digital beer - with the last few weeks I've had at work. Better make it a digital (double) scotch! :DThese last few weeks (since mid-April) have been awful for me too. Do you suppose that the universe has taken some kind of unusually perverse twist against us "denizens of databases" lately?
-PatP
How do deduplication on Fact Table
Hi, everyone,
l've a fact table DEVICE with following structure,
DEVICE_NAME VARCHAR(50)
DEVICE_DATE DATETIME
DEVICE_NUMBER INT
Where DEVICE_NAME and DEVICE_DATE form a PRIMARY KEY
So l would like to import a text file with same information into this table.
My problem is, text file contains records which will violate my primary key constraint. In that case, l would only insert the record with DEVICE_NUMER not equal to ZERO and discard and log the others.
In case of the records violtae primary key constraints have DEVICE_NUMBER not equal to ZERO, discard both and log it.
So anyone has good suggestion on this?
In the Data Flow you can check for all of these situations. Start with a text file source.
To prevent insertion of record with zero device_number, use a Conditional Split transform. For anew output, write an expression to test for DEVICE_NUMBER == 0. Any rows that match will follow that output. Connect the following component to the default output, thus discarding those rows.
To prevent insertion of rows that already exist, use a Lookup. Set the Error Configuration, to Redirect errors. This means that when the lookup does not find a match, those "new" rows will flow to the error output, and "matched" rows will flow doen the default output. Connect the following component to the error output, again discarding the matched rows.
If you suspect you have new rows, but they can be duplicates within the file itself, use an Aggregate transform, which has the option to provide unique rows only.
How do can I do this?
Hi,
I have this table:
colA ; colB
1) 1 2
2) 2 1
3) 2 6
4) 3 6
5) 6 3
6) 6 7
7) 7 6
and I need to select from it only the rows that do not have an opposite pair, i.e. only line (3) as it is 2 - 6 and there is no line 6 - 2, all the other lines have opposites, like line (1) 1 - 2 has line (2) 2 - 1 and line (4) has line (5), etc.
any ideas?
thanks.
SELECT b.id, b.colA, b.colB FROM mytest b WHERE b.id NOT IN (SELECT a.id
FROM myTest a INNER JOIN (SELECT id, colA, colB
FROM myTest ) d ON a.colA=d.colB AND a.colB=d.colA)
|||thanks a lot,
but I have only colA and colB, the "x)" in my post was only for being more clear about it.
|||Maybe something like this:
|||
declare @.mock table (colA int, colB int)
insert into @.mock values (1, 2)
insert into @.mock values (2, 1)
insert into @.mock values (2, 6)
insert into @.mock values (3, 6)
insert into @.Mock values (6, 3)
insert into @.mock values (6, 7)
insert into @.mock values (7, 6)select *
from @.mock x
where not exists
( select 0 from @.mock y
where x.cola = y.colb
and x.colb = y.cola
)
-- - Output: --- colA colB
-- -- --
-- 2 6
Hi,
thanks to all but here is the solution:
SELECT *
FROM (SELECT * FROM tbl) AS t3
EXCEPT
SELECT *
FROM (SELECT t1.*
FROM tbl AS t1 INNER JOIN
tbl AS t2
on t1.colA = t2.colB AND
t1.colB = t2.colA) AS t4
better to know the new operators... :)
|||Thank you, Alan; you caught me. I have missed the EXCEPT join at least once before, too. Please keep after me until I get it right. :-)
|||Using NOT EXISTS is the easiest way to write the query. This version using EXCEPT and joins will be much slower.
Dave
How Do Add a DB Connection to a Table in SQL Server Express using Visual Web Developer?
I'm developing using Visual Web Developer and want to have a web page that shows the contents of a table. I get the error message when testing the connection through database explorer "Login failed for user ''. The user is not associated with a trusted SQL Server connection".
The database is located on a server running IIS and has SQL Server Express installed.
u may need to change the connection string to something like
Server=ServerAddress;Database=DataBase;User ID=Username;Password=Password;Trusted_Connection=False
How Do Add a DB Connection to a Table in SQL Server Express using Visual Web Developer?
I'm developing using Visual Web Developer and want to have a web page that shows the contents of a table. I get the error message when testing the connection through database explorer "Login failed for user ''. The user is not associated with a trusted SQL Server connection".
The database is located on a server running IIS and has SQL Server Express installed.
From MS:
During a logon process to SQL server the following error may appear:"Login failed for user 'username'. The user is not associated with atrusted SQL Server connection. (Microsoft SQL Server, Error: 18452)".
The SQL server has been configured to operate in "Windows Authentication Mode (Windows Authentication)" and doesn't allow
the use of SQL accounts.
Change the Authentication Mode of the SQL server from "Windows Authentication Mode (Windows Authentication)"
to "Mixed Mode (Windows Authentication and SQL Server Authentication)".
Hope this helps
sqlMonday, March 26, 2012
How disable IDENTITY on column
Hello,
I have big problem with IDENTITY column in table. I must disable this function in 500 tables in my system and i don`t know how do this :( is it such way in order to do this ?
INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel')GO
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT dbo.Tool ON
GO
-- Try to insert an explicit ID value of 3.
INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel')
GO|||Yes I know this manner, but I can`t disable IDENTITY on all tables because system say me that I can use this function only one table in this moment and don`t allow me disable IDENTITY on tables.|||
something to start with
|||use northwind
select IDENTITY(int, 1,1) AS ID_Num, name AS NAME into #alltables from sysobjects where xtype='u'
BEGIN TRANSACTION
declare @.ctr int
select @.ctr=0
DECLARE @.CMD NVARCHAR(200)
while @.CTR<>(SELECT MAX(ID_NUM) FROM #alltables )
BEGIN
sELECT @.CTR=@.CTR+1
SELECT @.CMD= 'SET IDENTITY_INSERT '+NAME + ' ON' FROM #ALLTABLES WHERE ID_NUM=@.CTR
EXEC (@.CMD)
ENDROLLBACK TRANSACTION
SELECT * FROM #ALLTABLESrollback transaction
I am not clear on what you want to do. Do you want to disable it temporarily on multiple tables? Or eliminate it permanently? I can whip up the basis of a routine to change the identity column to no longer be an identity column, but from a further reply that doesn't seem to be what you want.
You can only use SET IDENTITY_INSERT ON on only one table at a time, per connection, but this should be acceptable because you can only insert into one table at a time per connection. So if this is a temporary thing, then all you need to do is just turn it off for the table you are working on.
Expand and a better answer can possibly be arrived at.
|||I understand. :( it`s a pity that we can`t disable this function on all table on one moment... thanx for help|||hey take a look at this
use northwind
set xact_abort off
select IDENTITY(int, 1,1) AS ID_Num, name AS NAME into #alltables from sysobjects where xtype='u'
BEGIN TRANSACTION
declare @.ctr int
select @.ctr=0
DECLARE @.CMD NVARCHAR(200)
while @.CTR<>(SELECT MAX(ID_NUM) FROM #alltables )
BEGIN
sELECT @.CTR=@.CTR+1
SELECT @.CMD= 'SET IDENTITY_INSERT '+NAME + ' ON' FROM #ALLTABLES WHERE ID_NUM=@.CTR
EXEC (@.CMD)
END
it says 33 rows affected
|||Try this: DECLARE @.Statement nvarchar(2000)
DECLARE Statements CURSOR LOCAL FAST_FORWARD
FOR SELECT N'SET IDENTITY_INSERT ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' + QUOTENAME(TABLE_NAME) + ' OFF'
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN Statements
FETCH NEXT FROM Statements INTO @.Statement
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Statements INTO @.Statement
BEGIN TRY
EXEC(@.Statement)
END TRY
BEGIN CATCH
SELECT 'Error:'+ERROR_MESSAGE()+' on:'+@.Statement
END CATCH
END
-- Clean up work
CLOSE Statements
If you are sure that all the tables have identity columns you can use this:
sp_msforeachtable @.command1="print '?'", @.command2="SET IDENTITY_INSERT ? OFF"
How disable IDENTITY on column
Hello,
I have big problem with IDENTITY column in table. I must disable this function in 500 tables in my system and i don`t know how do this :( is it such way in order to do this ?
INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel')GO
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT dbo.Tool ON
GO
-- Try to insert an explicit ID value of 3.
INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel')
GO|||Yes I know this manner, but I can`t disable IDENTITY on all tables because system say me that I can use this function only one table in this moment and don`t allow me disable IDENTITY on tables.|||
something to start with
|||use northwind
select IDENTITY(int, 1,1) AS ID_Num, name AS NAME into #alltables from sysobjects where xtype='u'
BEGIN TRANSACTION
declare @.ctr int
select @.ctr=0
DECLARE @.CMD NVARCHAR(200)
while @.CTR<>(SELECT MAX(ID_NUM) FROM #alltables )
BEGIN
sELECT @.CTR=@.CTR+1
SELECT @.CMD= 'SET IDENTITY_INSERT '+NAME + ' ON' FROM #ALLTABLES WHERE ID_NUM=@.CTR
EXEC (@.CMD)
ENDROLLBACK TRANSACTION
SELECT * FROM #ALLTABLESrollback transaction
I am not clear on what you want to do. Do you want to disable it temporarily on multiple tables? Or eliminate it permanently? I can whip up the basis of a routine to change the identity column to no longer be an identity column, but from a further reply that doesn't seem to be what you want.
You can only use SET IDENTITY_INSERT ON on only one table at a time, per connection, but this should be acceptable because you can only insert into one table at a time per connection. So if this is a temporary thing, then all you need to do is just turn it off for the table you are working on.
Expand and a better answer can possibly be arrived at.
|||I understand. :( it`s a pity that we can`t disable this function on all table on one moment... thanx for help|||hey take a look at this
use northwind
set xact_abort off
select IDENTITY(int, 1,1) AS ID_Num, name AS NAME into #alltables from sysobjects where xtype='u'
BEGIN TRANSACTION
declare @.ctr int
select @.ctr=0
DECLARE @.CMD NVARCHAR(200)
while @.CTR<>(SELECT MAX(ID_NUM) FROM #alltables )
BEGIN
sELECT @.CTR=@.CTR+1
SELECT @.CMD= 'SET IDENTITY_INSERT '+NAME + ' ON' FROM #ALLTABLES WHERE ID_NUM=@.CTR
EXEC (@.CMD)
END
it says 33 rows affected
|||Try this: DECLARE @.Statement nvarchar(2000)
DECLARE Statements CURSOR LOCAL FAST_FORWARD
FOR SELECT N'SET IDENTITY_INSERT ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' + QUOTENAME(TABLE_NAME) + ' OFF'
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN Statements
FETCH NEXT FROM Statements INTO @.Statement
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Statements INTO @.Statement
BEGIN TRY
EXEC(@.Statement)
END TRY
BEGIN CATCH
SELECT 'Error:'+ERROR_MESSAGE()+' on:'+@.Statement
END CATCH
END
-- Clean up work
CLOSE Statements
If you are sure that all the tables have identity columns you can use this:
sp_msforeachtable @.command1="print '?'", @.command2="SET IDENTITY_INSERT ? OFF"sql
how create table with variable name
I want to create a table with variable name but can't? how
declare @.k as char(100)
set @.k = 't1'
create table @.k << create table with name=@.k
thanks
tarvirdiDECLARE @.OrderCounts TABLE(ProductID int, OrderCount int)
INSERT @.OrderCounts values (1, 1)
select * from @.OrderCounts
"Tarvirdi" <m_tarvirdi@.isc.iranet.net> wrote in message
news:%23PJ4AJsjGHA.5020@.TK2MSFTNGP02.phx.gbl...
>I want to create a table with variable name but can't? how
> declare @.k as char(100)
> set @.k = 't1'
> create table @.k << create table with name=@.k
> thanks
> tarvirdi
>|||Use dynamic SQL to create the table (using youe example):
declare @.k as char(100)
declare @.sql as varchar(200)
set @.k = 't1'
set @.sql = 'create table ' + @.k
EXEC(@.sql)
"Tarvirdi" wrote:
> I want to create a table with variable name but can't? how
> declare @.k as char(100)
> set @.k = 't1'
> create table @.k << create table with name=@.k
> thanks
> tarvirdi
>
>
declare @.k as char(100)
set @.k = 't1'
create table @.k << create table with name=@.k
thanks
tarvirdiDECLARE @.OrderCounts TABLE(ProductID int, OrderCount int)
INSERT @.OrderCounts values (1, 1)
select * from @.OrderCounts
"Tarvirdi" <m_tarvirdi@.isc.iranet.net> wrote in message
news:%23PJ4AJsjGHA.5020@.TK2MSFTNGP02.phx.gbl...
>I want to create a table with variable name but can't? how
> declare @.k as char(100)
> set @.k = 't1'
> create table @.k << create table with name=@.k
> thanks
> tarvirdi
>|||Use dynamic SQL to create the table (using youe example):
declare @.k as char(100)
declare @.sql as varchar(200)
set @.k = 't1'
set @.sql = 'create table ' + @.k
EXEC(@.sql)
"Tarvirdi" wrote:
> I want to create a table with variable name but can't? how
> declare @.k as char(100)
> set @.k = 't1'
> create table @.k << create table with name=@.k
> thanks
> tarvirdi
>
>
Friday, March 23, 2012
How could I use row as columns?
create table t2
(
umc varchar(20),
outdate datetime,
outnumber int
)
insert t2 values (1,'2005-2-5',1)
insert t2 values (2,'2005-2-5',1)
insert t2 values (2,'2005-2-6',1)
insert t2 values (3,'2005-2-5',2)
insert t2 values (3,'2005-2-6',1)
insert t2 values (4,'2005-2-7',1)
I hope the result to be
(2005-2-5,2005-2-6,2005-2-7 is column name now)
2005-2-5 2005-2-6 2005-2-7
1 1 0 0
2 1 1 0
3 2 1 0
4 0 0 1
Can I just compose it with SELECT statement?First you have to select distinct dates into a cursor,
than select from the table left outer join each date where date from the
table = date of the column.
"XXY" <xxy02021@.NOSPAM.163.com> wrote in message
news:eUkwCXNEFHA.2508@.TK2MSFTNGP09.phx.gbl...
> create table t2
> (
> umc varchar(20),
> outdate datetime,
> outnumber int
> )
> insert t2 values (1,'2005-2-5',1)
> insert t2 values (2,'2005-2-5',1)
> insert t2 values (2,'2005-2-6',1)
> insert t2 values (3,'2005-2-5',2)
> insert t2 values (3,'2005-2-6',1)
> insert t2 values (4,'2005-2-7',1)
> I hope the result to be
> (2005-2-5,2005-2-6,2005-2-7 is column name now)
> 2005-2-5 2005-2-6 2005-2-7
> 1 1 0 0
> 2 1 1 0
> 3 2 1 0
> 4 0 0 1
> Can I just compose it with SELECT statement?
>|||http://aspfaq.com/show.asp?id=2462
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"XXY" <xxy02021@.NOSPAM.163.com> wrote in message
news:eUkwCXNEFHA.2508@.TK2MSFTNGP09.phx.gbl...
> create table t2
> (
> umc varchar(20),
> outdate datetime,
> outnumber int
> )
> insert t2 values (1,'2005-2-5',1)
> insert t2 values (2,'2005-2-5',1)
> insert t2 values (2,'2005-2-6',1)
> insert t2 values (3,'2005-2-5',2)
> insert t2 values (3,'2005-2-6',1)
> insert t2 values (4,'2005-2-7',1)
> I hope the result to be
> (2005-2-5,2005-2-6,2005-2-7 is column name now)
> 2005-2-5 2005-2-6 2005-2-7
> 1 1 0 0
> 2 1 1 0
> 3 2 1 0
> 4 0 0 1
> Can I just compose it with SELECT statement?
>|||SELECT umc,
SUM(CASE WHEN DATEDIFF(DAY,@.dt,outdate)=0 THEN outnumber ELSE 0 END),
SUM(CASE WHEN DATEDIFF(DAY,@.dt,outdate)=1 THEN outnumber ELSE 0 END),
SUM(CASE WHEN DATEDIFF(DAY,@.dt,outdate)=2 THEN outnumber ELSE 0 END)
FROM T2
WHERE outdate >= @.dt
AND outdate < DATEADD(DAY,3,@.dt)
GROUP BY umc
Column names in a query are fixed so dynamic SQL would be required to
change the names based on the data. That shouldn't really be a problem
though. It should be easy enough to display different column names in
your client application.
David Portas
SQL Server MVP
--|||Nadim,
Thanks so much and that's what I want, however, is it possible for you to
show me some sample codes based on my DDL?
yours, XXY
"Nadim Wakim" <nadimlb@.cyberia.net.lb>
:uOjnD2NEFHA.1392@.tk2msftngp13.phx.gbl...
> First you have to select distinct dates into a cursor,
> than select from the table left outer join each date where date from the
> table = date of the column.
>
> "XXY" <xxy02021@.NOSPAM.163.com> wrote in message
> news:eUkwCXNEFHA.2508@.TK2MSFTNGP09.phx.gbl...
>|||Hi David and Roji,
I do appreciated your articles and sample codes, however when the outdate
ranges much(it might be any day in a year in my table), I am afraid it is
not a good idea using datediff. Don't you think so ?
yours, XXY
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org>
:1108198808.232843.84220@.g14g2000cwa.googlegroups.com...
> SELECT umc,
> SUM(CASE WHEN DATEDIFF(DAY,@.dt,outdate)=0 THEN outnumber ELSE 0 END),
> SUM(CASE WHEN DATEDIFF(DAY,@.dt,outdate)=1 THEN outnumber ELSE 0 END),
> SUM(CASE WHEN DATEDIFF(DAY,@.dt,outdate)=2 THEN outnumber ELSE 0 END)
> FROM T2
> WHERE outdate >= @.dt
> AND outdate < DATEADD(DAY,3,@.dt)
> GROUP BY umc
> Column names in a query are fixed so dynamic SQL would be required to
> change the names based on the data. That shouldn't really be a problem
> though. It should be easy enough to display different column names in
> your client application.
> --
> David Portas
> SQL Server MVP
> --
>|||Roji, Thanks so much!!!
I read http://www.sqlteam.com/item.asp?ItemID=2955 and got the right answer
from
exec crosstab 'select umc from t2 group by
umc','sum(outnumber)','outdate','t2'
You are the MAN!!
"XXY" <xxy02021@.NOSPAM.163.com> д?
:eUkwCXNEFHA.2508@.TK2MSFTNGP09.phx.gbl...
> create table t2
> (
> umc varchar(20),
> outdate datetime,
> outnumber int
> )
> insert t2 values (1,'2005-2-5',1)
> insert t2 values (2,'2005-2-5',1)
> insert t2 values (2,'2005-2-6',1)
> insert t2 values (3,'2005-2-5',2)
> insert t2 values (3,'2005-2-6',1)
> insert t2 values (4,'2005-2-7',1)
> I hope the result to be
> (2005-2-5,2005-2-6,2005-2-7 is column name now)
> 2005-2-5 2005-2-6 2005-2-7
> 1 1 0 0
> 2 1 1 0
> 3 2 1 0
> 4 0 0 1
> Can I just compose it with SELECT statement?
>|||I'm reminded of the Di-Tech commercials:( :)
www.rac4sql.net
"XXY" <xxy02021@.NOSPAM.163.com> wrote in message
news:%23kdz4APEFHA.2608@.TK2MSFTNGP10.phx.gbl...
> Roji, Thanks so much!!!
> I read http://www.sqlteam.com/item.asp?ItemID=2955 and got the right
> answer
> from
> exec crosstab 'select umc from t2 group by
> umc','sum(outnumber)','outdate','t2'
> You are the MAN!!
>
> "XXY" <xxy02021@.NOSPAM.163.com> д?
> :eUkwCXNEFHA.2508@.TK2MSFTNGP09.phx.gbl...
>|||I don't see a problem. The date range selection is in the WHERE clause
and is sargable. The cost of DATEDIFF should be relatively light but if
performance is a concern then you should test it out with your typical
data-set.
David Portas
SQL Server MVP
--sql
(
umc varchar(20),
outdate datetime,
outnumber int
)
insert t2 values (1,'2005-2-5',1)
insert t2 values (2,'2005-2-5',1)
insert t2 values (2,'2005-2-6',1)
insert t2 values (3,'2005-2-5',2)
insert t2 values (3,'2005-2-6',1)
insert t2 values (4,'2005-2-7',1)
I hope the result to be
(2005-2-5,2005-2-6,2005-2-7 is column name now)
2005-2-5 2005-2-6 2005-2-7
1 1 0 0
2 1 1 0
3 2 1 0
4 0 0 1
Can I just compose it with SELECT statement?First you have to select distinct dates into a cursor,
than select from the table left outer join each date where date from the
table = date of the column.
"XXY" <xxy02021@.NOSPAM.163.com> wrote in message
news:eUkwCXNEFHA.2508@.TK2MSFTNGP09.phx.gbl...
> create table t2
> (
> umc varchar(20),
> outdate datetime,
> outnumber int
> )
> insert t2 values (1,'2005-2-5',1)
> insert t2 values (2,'2005-2-5',1)
> insert t2 values (2,'2005-2-6',1)
> insert t2 values (3,'2005-2-5',2)
> insert t2 values (3,'2005-2-6',1)
> insert t2 values (4,'2005-2-7',1)
> I hope the result to be
> (2005-2-5,2005-2-6,2005-2-7 is column name now)
> 2005-2-5 2005-2-6 2005-2-7
> 1 1 0 0
> 2 1 1 0
> 3 2 1 0
> 4 0 0 1
> Can I just compose it with SELECT statement?
>|||http://aspfaq.com/show.asp?id=2462
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"XXY" <xxy02021@.NOSPAM.163.com> wrote in message
news:eUkwCXNEFHA.2508@.TK2MSFTNGP09.phx.gbl...
> create table t2
> (
> umc varchar(20),
> outdate datetime,
> outnumber int
> )
> insert t2 values (1,'2005-2-5',1)
> insert t2 values (2,'2005-2-5',1)
> insert t2 values (2,'2005-2-6',1)
> insert t2 values (3,'2005-2-5',2)
> insert t2 values (3,'2005-2-6',1)
> insert t2 values (4,'2005-2-7',1)
> I hope the result to be
> (2005-2-5,2005-2-6,2005-2-7 is column name now)
> 2005-2-5 2005-2-6 2005-2-7
> 1 1 0 0
> 2 1 1 0
> 3 2 1 0
> 4 0 0 1
> Can I just compose it with SELECT statement?
>|||SELECT umc,
SUM(CASE WHEN DATEDIFF(DAY,@.dt,outdate)=0 THEN outnumber ELSE 0 END),
SUM(CASE WHEN DATEDIFF(DAY,@.dt,outdate)=1 THEN outnumber ELSE 0 END),
SUM(CASE WHEN DATEDIFF(DAY,@.dt,outdate)=2 THEN outnumber ELSE 0 END)
FROM T2
WHERE outdate >= @.dt
AND outdate < DATEADD(DAY,3,@.dt)
GROUP BY umc
Column names in a query are fixed so dynamic SQL would be required to
change the names based on the data. That shouldn't really be a problem
though. It should be easy enough to display different column names in
your client application.
David Portas
SQL Server MVP
--|||Nadim,
Thanks so much and that's what I want, however, is it possible for you to
show me some sample codes based on my DDL?
yours, XXY
"Nadim Wakim" <nadimlb@.cyberia.net.lb>
:uOjnD2NEFHA.1392@.tk2msftngp13.phx.gbl...
> First you have to select distinct dates into a cursor,
> than select from the table left outer join each date where date from the
> table = date of the column.
>
> "XXY" <xxy02021@.NOSPAM.163.com> wrote in message
> news:eUkwCXNEFHA.2508@.TK2MSFTNGP09.phx.gbl...
>|||Hi David and Roji,
I do appreciated your articles and sample codes, however when the outdate
ranges much(it might be any day in a year in my table), I am afraid it is
not a good idea using datediff. Don't you think so ?
yours, XXY
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org>
:1108198808.232843.84220@.g14g2000cwa.googlegroups.com...
> SELECT umc,
> SUM(CASE WHEN DATEDIFF(DAY,@.dt,outdate)=0 THEN outnumber ELSE 0 END),
> SUM(CASE WHEN DATEDIFF(DAY,@.dt,outdate)=1 THEN outnumber ELSE 0 END),
> SUM(CASE WHEN DATEDIFF(DAY,@.dt,outdate)=2 THEN outnumber ELSE 0 END)
> FROM T2
> WHERE outdate >= @.dt
> AND outdate < DATEADD(DAY,3,@.dt)
> GROUP BY umc
> Column names in a query are fixed so dynamic SQL would be required to
> change the names based on the data. That shouldn't really be a problem
> though. It should be easy enough to display different column names in
> your client application.
> --
> David Portas
> SQL Server MVP
> --
>|||Roji, Thanks so much!!!
I read http://www.sqlteam.com/item.asp?ItemID=2955 and got the right answer
from
exec crosstab 'select umc from t2 group by
umc','sum(outnumber)','outdate','t2'
You are the MAN!!
"XXY" <xxy02021@.NOSPAM.163.com> д?
:eUkwCXNEFHA.2508@.TK2MSFTNGP09.phx.gbl...
> create table t2
> (
> umc varchar(20),
> outdate datetime,
> outnumber int
> )
> insert t2 values (1,'2005-2-5',1)
> insert t2 values (2,'2005-2-5',1)
> insert t2 values (2,'2005-2-6',1)
> insert t2 values (3,'2005-2-5',2)
> insert t2 values (3,'2005-2-6',1)
> insert t2 values (4,'2005-2-7',1)
> I hope the result to be
> (2005-2-5,2005-2-6,2005-2-7 is column name now)
> 2005-2-5 2005-2-6 2005-2-7
> 1 1 0 0
> 2 1 1 0
> 3 2 1 0
> 4 0 0 1
> Can I just compose it with SELECT statement?
>|||I'm reminded of the Di-Tech commercials:( :)
www.rac4sql.net
"XXY" <xxy02021@.NOSPAM.163.com> wrote in message
news:%23kdz4APEFHA.2608@.TK2MSFTNGP10.phx.gbl...
> Roji, Thanks so much!!!
> I read http://www.sqlteam.com/item.asp?ItemID=2955 and got the right
> answer
> from
> exec crosstab 'select umc from t2 group by
> umc','sum(outnumber)','outdate','t2'
> You are the MAN!!
>
> "XXY" <xxy02021@.NOSPAM.163.com> д?
> :eUkwCXNEFHA.2508@.TK2MSFTNGP09.phx.gbl...
>|||I don't see a problem. The date range selection is in the WHERE clause
and is sargable. The cost of DATEDIFF should be relatively light but if
performance is a concern then you should test it out with your typical
data-set.
David Portas
SQL Server MVP
--sql
How could I get the records recusrively (nested)?
How could I get the records recusrively (nested)?
I've a table ACCT with columns as follows.
ACCT_CD varchar(20)
TYPE_CD varchar(1)
It has following data.
ACCT1 F
ACCT10 F
ACCT2 F
ACCT3 F
ACCT4 F
ACCT5 F
ACCT6 F
ACCT7 F
ACCT8 F
ACCT9 F
GRP_1 G
GRP_2 G
SGRP_1 C
SGROUP C
I've another table ACCT_REL with columns as follows.
PARENT_ACCT varchar(20)
CHILD_ACCT varchar(20)
REL_TYP varchar(1)
It has following data.
SELECT * FROM ACCT_REL WHERE PARENT_ACCT = 'SGROUP'
SGROUP ACCT1 C
SGROUP ACCT2 C
SGROUP GRP_2 C
SGROUP SGRP_1 C
SELECT * FROM ACCT_REL WHERE PARENT_ACCT = 'GRP_1'
GRP_1 ACCT3 G
GRP_1 ACCT4 G
GRP_1 ACCT5 G
SELECT * FROM ACCT_REL WHERE PARENT_ACCT = 'GRP_2'
GRP_2 ACCT6 G
GRP_2 ACCT7 G
GRP_2 ACCT8 G
SELECT * FROM ACCT_REL WHERE PARENT_ACCT = 'SGRP_1'
SGRP_1 GRP_1 C
SGRP_1 ACCT9 C
SGRP_1 ACCT10 C
I want retrive all the child_acct for PARENT_ACCT = 'SGROUP'. If the
CHILD_ACCT has some records in the ACCT_REL table, then I would like to
get them also. It could have many levels of nesting. How could I get the
records recusrively?
E.g. In the above example, the expected result could be:
ACCT1
ACCT10
ACCT2
ACCT3
ACCT4
ACCT5
ACCT6
ACCT7
ACCT8
ACCT9
GRP_1
GRP_2
SGRP_1
Thanks,
DJ
*** Sent via Developersdex http://www.examnotes.net ***check this out... (on behalf of ML :)
http://milambda.blogspot.com/2005/0...or-monkeys.html|||Also check out CTEs (Common Table Expressions) if you are using 2005.
There is a good example posted in Omni's blog here:
http://omnibuzz-sql.blogspot.com/20...vs.ht
ml
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:9CB52622-90FB-4E32-9F92-CC3456633DDD@.microsoft.com...
> check this out... (on behalf of ML :)
> http://milambda.blogspot.com/2005/0...or-monkeys.html
>|||Get a copy of TREES & HIERARCHJIES IN SQL for several methods of
modeling this kidn of data. You do not need recursive procedural code
in the Nesteed sets model and do this in one simple query.|||
Can you please tell me how to do this?
An example would be helpful.
Thanks.
*** Sent via Developersdex http://www.examnotes.net ***|||I beleive --CELKO-- is suggesting that you change your data model to store
the data in a tree format.
Omni's post contains one method for doing this.
CTEs on SQL Server 2005 allow you to do it with a single sql statement using
your current data model, but performance may not be as good.
If you do a search on "TREES & HIERARCHIES IN SQL", or just the phrases SQL
TREES HIERARCHIES, you will find several examples, including some articles
by Joe (--CELKO--).
"DJ" <dominic_koyappillil@.yahoo.com> wrote in message
news:uQqaGahiGHA.3572@.TK2MSFTNGP04.phx.gbl...
>
> Can you please tell me how to do this?
> An example would be helpful.
> Thanks.
> *** Sent via Developersdex http://www.examnotes.net ***
I've a table ACCT with columns as follows.
ACCT_CD varchar(20)
TYPE_CD varchar(1)
It has following data.
ACCT1 F
ACCT10 F
ACCT2 F
ACCT3 F
ACCT4 F
ACCT5 F
ACCT6 F
ACCT7 F
ACCT8 F
ACCT9 F
GRP_1 G
GRP_2 G
SGRP_1 C
SGROUP C
I've another table ACCT_REL with columns as follows.
PARENT_ACCT varchar(20)
CHILD_ACCT varchar(20)
REL_TYP varchar(1)
It has following data.
SELECT * FROM ACCT_REL WHERE PARENT_ACCT = 'SGROUP'
SGROUP ACCT1 C
SGROUP ACCT2 C
SGROUP GRP_2 C
SGROUP SGRP_1 C
SELECT * FROM ACCT_REL WHERE PARENT_ACCT = 'GRP_1'
GRP_1 ACCT3 G
GRP_1 ACCT4 G
GRP_1 ACCT5 G
SELECT * FROM ACCT_REL WHERE PARENT_ACCT = 'GRP_2'
GRP_2 ACCT6 G
GRP_2 ACCT7 G
GRP_2 ACCT8 G
SELECT * FROM ACCT_REL WHERE PARENT_ACCT = 'SGRP_1'
SGRP_1 GRP_1 C
SGRP_1 ACCT9 C
SGRP_1 ACCT10 C
I want retrive all the child_acct for PARENT_ACCT = 'SGROUP'. If the
CHILD_ACCT has some records in the ACCT_REL table, then I would like to
get them also. It could have many levels of nesting. How could I get the
records recusrively?
E.g. In the above example, the expected result could be:
ACCT1
ACCT10
ACCT2
ACCT3
ACCT4
ACCT5
ACCT6
ACCT7
ACCT8
ACCT9
GRP_1
GRP_2
SGRP_1
Thanks,
DJ
*** Sent via Developersdex http://www.examnotes.net ***check this out... (on behalf of ML :)
http://milambda.blogspot.com/2005/0...or-monkeys.html|||Also check out CTEs (Common Table Expressions) if you are using 2005.
There is a good example posted in Omni's blog here:
http://omnibuzz-sql.blogspot.com/20...vs.ht
ml
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:9CB52622-90FB-4E32-9F92-CC3456633DDD@.microsoft.com...
> check this out... (on behalf of ML :)
> http://milambda.blogspot.com/2005/0...or-monkeys.html
>|||Get a copy of TREES & HIERARCHJIES IN SQL for several methods of
modeling this kidn of data. You do not need recursive procedural code
in the Nesteed sets model and do this in one simple query.|||
Can you please tell me how to do this?
An example would be helpful.
Thanks.
*** Sent via Developersdex http://www.examnotes.net ***|||I beleive --CELKO-- is suggesting that you change your data model to store
the data in a tree format.
Omni's post contains one method for doing this.
CTEs on SQL Server 2005 allow you to do it with a single sql statement using
your current data model, but performance may not be as good.
If you do a search on "TREES & HIERARCHIES IN SQL", or just the phrases SQL
TREES HIERARCHIES, you will find several examples, including some articles
by Joe (--CELKO--).
"DJ" <dominic_koyappillil@.yahoo.com> wrote in message
news:uQqaGahiGHA.3572@.TK2MSFTNGP04.phx.gbl...
>
> Can you please tell me how to do this?
> An example would be helpful.
> Thanks.
> *** Sent via Developersdex http://www.examnotes.net ***
How could I create an end user sort report?
Hi all,
I want to create a report that let end user sort the table by click head
of the any column, ascending and descending. How to do that in reporting
service?
My reporting service is in Visual Statdio .Net 2003
Best Regards,
SebastianHi Sebastian
I have not had to do this but would expect that one way would be to:
1) Have the order determined by a hidden parameter (eg intSort = 1
sorts by Name, intSort = 2 sorts by City)
2) use labels for the sort column names using Navigation properties to
call the report again with a parameter indicating the sort required.
I"ve done something similar to allow dynamic grouping
Hope that helps
BrianK
www.bolign.com
I want to create a report that let end user sort the table by click head
of the any column, ascending and descending. How to do that in reporting
service?
My reporting service is in Visual Statdio .Net 2003
Best Regards,
SebastianHi Sebastian
I have not had to do this but would expect that one way would be to:
1) Have the order determined by a hidden parameter (eg intSort = 1
sorts by Name, intSort = 2 sorts by City)
2) use labels for the sort column names using Navigation properties to
call the report again with a parameter indicating the sort required.
I"ve done something similar to allow dynamic grouping
Hope that helps
BrianK
www.bolign.com
how copy table
How I make sql queries for coping one table (exist) to another table
(no exist)One method:
INSERT INTO AnotherTable
SELECT *
FROM OneTable
WHERE NOT EXISTS
(
SELECT *
FROM AnotherTable
WHERE AnotherTable.PK = OneTable.PK
)
Hope this helps.
Dan Guzman
SQL Server MVP
<f1414@.mail.ru> wrote in message
news:pje101lo8a0j5lkltd5ftuhd90ja7rmft9@.
4ax.com...
> How I make sql queries for coping one table (exist) to another table
> (no exist)|||SELECT * INTO NewTable FROM OldTable WHERE 1=1 (with data) OR WHERE 1=2
(without)
Note, that it does not transfer neither a PK,FK,indexes,Constraints
<f1414@.mail.ru> wrote in message
news:pje101lo8a0j5lkltd5ftuhd90ja7rmft9@.
4ax.com...
> How I make sql queries for coping one table (exist) to another table
> (no exist)|||SELECT *
INTO NewTable
FROM ExistingTable
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
<f1414@.mail.ru> wrote in message
news:pje101lo8a0j5lkltd5ftuhd90ja7rmft9@.
4ax.com...
> How I make sql queries for coping one table (exist) to another table
> (no exist)
(no exist)One method:
INSERT INTO AnotherTable
SELECT *
FROM OneTable
WHERE NOT EXISTS
(
SELECT *
FROM AnotherTable
WHERE AnotherTable.PK = OneTable.PK
)
Hope this helps.
Dan Guzman
SQL Server MVP
<f1414@.mail.ru> wrote in message
news:pje101lo8a0j5lkltd5ftuhd90ja7rmft9@.
4ax.com...
> How I make sql queries for coping one table (exist) to another table
> (no exist)|||SELECT * INTO NewTable FROM OldTable WHERE 1=1 (with data) OR WHERE 1=2
(without)
Note, that it does not transfer neither a PK,FK,indexes,Constraints
<f1414@.mail.ru> wrote in message
news:pje101lo8a0j5lkltd5ftuhd90ja7rmft9@.
4ax.com...
> How I make sql queries for coping one table (exist) to another table
> (no exist)|||SELECT *
INTO NewTable
FROM ExistingTable
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
<f1414@.mail.ru> wrote in message
news:pje101lo8a0j5lkltd5ftuhd90ja7rmft9@.
4ax.com...
> How I make sql queries for coping one table (exist) to another table
> (no exist)
Subscribe to:
Posts (Atom)