Hi,
I am writing some updates scripts for a database and need to add a new
column at a specific position in the table. I am using the 'ALTER TABLE'
command but this just tags it on the end. Is there any way of doing (via
TSQL) this without rebuilding the table?
Thanks in advance,
Stuno, there isn't. but there isn't a good reason for doing this in the first
place - at least not the one i know of. why is the order of columns in base
table important to you?
dean
"Stu" <s.lock@.cergis.com> wrote in message
news:uL%230ep9KGHA.3836@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am writing some updates scripts for a database and need to add a new
> column at a specific position in the table. I am using the 'ALTER TABLE'
> command but this just tags it on the end. Is there any way of doing (via
> TSQL) this without rebuilding the table?
> Thanks in advance,
> Stu
>
>|||Stu skrev:
> Hi,
> I am writing some updates scripts for a database and need to add a new
> column at a specific position in the table. I am using the 'ALTER TABLE'
> command but this just tags it on the end. Is there any way of doing (via
> TSQL) this without rebuilding the table?
> Thanks in advance,
> Stu
This is a common question, and the anwer is 'no' :)
Search for it on Google for example, then you'll learn that you should
not really care about in which order the columns are in a table.
/impslayer, aka Birger Johansson|||Stu,
There is no good reason (that I have come across) to need the columns in any
particular order (except, perhaps, for clarity at some stage), but
operationally, they should make no difference whatsoever.
As a side point, they may make a difference should you use the SELECT * FROM
tblMyTable, but then again, that is not good practise to do, I always state
my Selected column names, for both clarity and to ensure that should a schem
a
change occur in the underlaying table (e.g. a column added) then the same,
correct, columns are returned each time.
Hope this assists,
Tony
"Stu" wrote:
> Hi,
> I am writing some updates scripts for a database and need to add a new
> column at a specific position in the table. I am using the 'ALTER TABLE'
> command but this just tags it on the end. Is there any way of doing (via
> TSQL) this without rebuilding the table?
> Thanks in advance,
> Stu
>
>|||http://www.aspfaq.com/2528
"Stu" <s.lock@.cergis.com> wrote in message
news:uL%230ep9KGHA.3836@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am writing some updates scripts for a database and need to add a new
> column at a specific position in the table. I am using the 'ALTER TABLE'
> command but this just tags it on the end. Is there any way of doing (via
> TSQL) this without rebuilding the table?
> Thanks in advance,
> Stu
>
>
Showing posts with label position. Show all posts
Showing posts with label position. Show all posts
Tuesday, March 27, 2012
Thursday, March 22, 2012
Adding a Column in an Ordinal Position
I have a situation where I am using SQL Server 2005 and I need to be able to
add a column in a specific ordinal position. I have looked at BOL and I
wasn't able to find any mention of being able to specify Ordinal position.
Is there a way?
Thanks!
RonLogically there should be no reason to put a column in a particular slot but
you can do this via SSMS. Open the table in the table designer and insert a
column in between the two that you want. Just be aware that this will
create a new table, copy all the existing rows to it, drop the old one and
rename this one. Not something you usually care to do in a production env
but that is up to you. There is no way to do this via an alter table.
Andrew J. Kelly SQL MVP
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:u0TTgYYBGHA.636@.TK2MSFTNGP10.phx.gbl...
> I have a situation where I am using SQL Server 2005 and I need to be able
> to add a column in a specific ordinal position. I have looked at BOL and
> I wasn't able to find any mention of being able to specify Ordinal
> position.
> Is there a way?
> Thanks!
> Ron
>|||> I have a situation where I am using SQL Server 2005 and I need to be able
> to add a column in a specific ordinal position. I have looked at BOL and
> I wasn't able to find any mention of being able to specify Ordinal
> position.
> Is there a way?
ALTER TABLE does not support this syntax because there is really no reason
to do this. Your applications and queries do not care what order the
columns were specified / created, unless you are lazy and expect ordinal
position from "SELECT * " or "INSERT without column definition" to work.
Of course, there are kludge workarounds. But I'd be more interested in
hearing the justification for this...
Please read the following:
http://www.aspfaq.com/2528|||Without recreating the entire table with the column in appropriate position
in the CREATE TABLE statement, this is not possible.
There could be some specific exceptions however, in most cases, adding a
column in a specific position is almost meaningless since well written SQL
code generally relies on column names rather than column positions.
Anith|||> ... But I'd be more interested in
> hearing the justification for this...
>
Well, I sometimes insert a column into the middle of a table out of
consideration for the y who maintains the system - who may be me or
may be some other guy. IMO, it is nice to have attributes that are
logically connected (begin and end dates, for example) display together
in the schema. It just makes for easier reading.
My 2 cents.
Payson|||> may be some other guy. IMO, it is nice to have attributes that are
> logically connected (begin and end dates, for example) display together
> in the schema.
Why would you have a BeginDate column, and then later on decide you need an
EndDate column?
If it's important, drop and re-create the table. If you're expecting an
ALTER TABLE exception, don't hold your breath. "Logically connected" does
not have to mean "visually adjacent." Another way to simulate this is to
create a view with the columns in the order you'd like them to be, if
disrupting the existing table is out of the question.
A|||Are you saying that a table only makes sense if the columns are properly
adjacent to each other? Or are you saying that no documentation is needed if
the columns are "well aligned"?
IMHO understanding databases and tables begins with understandig the
business logic first, and not the other way around.
ML
http://milambda.blogspot.com/|||ML wrote:
> Are you saying that a table only makes sense if the columns are properly
> adjacent to each other? Or are you saying that no documentation is needed
if
> the columns are "well aligned"?
>
No. Instead, I am saying it is easier to read and understand the
schema if columns that belong together display together in the schema.
And I almost never trust the documentation for details. I have been
burned too many times by inaccurate or out of date documentation and
comments. When learning a system, I always look at the schema and the
code because that's what's running. Maybe I do that because I have
been an independent contractor for so long. I seem to always be
cleaning up someone else's mess. Oh, well, it's a living...
Payson|||Yeah. Cleaning up someone else's mess is a drag. That's why I write my own
documentation before I actually start making changes. Sometimes the schema
and the code don't reflect the actual business logic as much as they reflect
the mind-set of the author. Sadly.
ML
http://milambda.blogspot.com/
add a column in a specific ordinal position. I have looked at BOL and I
wasn't able to find any mention of being able to specify Ordinal position.
Is there a way?
Thanks!
RonLogically there should be no reason to put a column in a particular slot but
you can do this via SSMS. Open the table in the table designer and insert a
column in between the two that you want. Just be aware that this will
create a new table, copy all the existing rows to it, drop the old one and
rename this one. Not something you usually care to do in a production env
but that is up to you. There is no way to do this via an alter table.
Andrew J. Kelly SQL MVP
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:u0TTgYYBGHA.636@.TK2MSFTNGP10.phx.gbl...
> I have a situation where I am using SQL Server 2005 and I need to be able
> to add a column in a specific ordinal position. I have looked at BOL and
> I wasn't able to find any mention of being able to specify Ordinal
> position.
> Is there a way?
> Thanks!
> Ron
>|||> I have a situation where I am using SQL Server 2005 and I need to be able
> to add a column in a specific ordinal position. I have looked at BOL and
> I wasn't able to find any mention of being able to specify Ordinal
> position.
> Is there a way?
ALTER TABLE does not support this syntax because there is really no reason
to do this. Your applications and queries do not care what order the
columns were specified / created, unless you are lazy and expect ordinal
position from "SELECT * " or "INSERT without column definition" to work.
Of course, there are kludge workarounds. But I'd be more interested in
hearing the justification for this...
Please read the following:
http://www.aspfaq.com/2528|||Without recreating the entire table with the column in appropriate position
in the CREATE TABLE statement, this is not possible.
There could be some specific exceptions however, in most cases, adding a
column in a specific position is almost meaningless since well written SQL
code generally relies on column names rather than column positions.
Anith|||> ... But I'd be more interested in
> hearing the justification for this...
>
Well, I sometimes insert a column into the middle of a table out of
consideration for the y who maintains the system - who may be me or
may be some other guy. IMO, it is nice to have attributes that are
logically connected (begin and end dates, for example) display together
in the schema. It just makes for easier reading.
My 2 cents.
Payson|||> may be some other guy. IMO, it is nice to have attributes that are
> logically connected (begin and end dates, for example) display together
> in the schema.
Why would you have a BeginDate column, and then later on decide you need an
EndDate column?
If it's important, drop and re-create the table. If you're expecting an
ALTER TABLE exception, don't hold your breath. "Logically connected" does
not have to mean "visually adjacent." Another way to simulate this is to
create a view with the columns in the order you'd like them to be, if
disrupting the existing table is out of the question.
A|||Are you saying that a table only makes sense if the columns are properly
adjacent to each other? Or are you saying that no documentation is needed if
the columns are "well aligned"?
IMHO understanding databases and tables begins with understandig the
business logic first, and not the other way around.
ML
http://milambda.blogspot.com/|||ML wrote:
> Are you saying that a table only makes sense if the columns are properly
> adjacent to each other? Or are you saying that no documentation is needed
if
> the columns are "well aligned"?
>
No. Instead, I am saying it is easier to read and understand the
schema if columns that belong together display together in the schema.
And I almost never trust the documentation for details. I have been
burned too many times by inaccurate or out of date documentation and
comments. When learning a system, I always look at the schema and the
code because that's what's running. Maybe I do that because I have
been an independent contractor for so long. I seem to always be
cleaning up someone else's mess. Oh, well, it's a living...
Payson|||Yeah. Cleaning up someone else's mess is a drag. That's why I write my own
documentation before I actually start making changes. Sometimes the schema
and the code don't reflect the actual business logic as much as they reflect
the mind-set of the author. Sadly.
ML
http://milambda.blogspot.com/
Thursday, March 8, 2012
Add Preceeding Zero
I have a field in a table, datatype is varchar. The field contains values
such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
want to change the field to precede all field values with 0 to yield,
01000000, 01000003, 05000001. I have attempted this using the follow but,
does not work returning error
UPDATE NumVal_1
SET NumVal = '0' & NumVal
WHERE Left(NumVal,1) > 0
Invalid operator for data type. Operator equals boolean AND, type equals
varchar
Am I even close to being on the right track?Your datatype for NumVal should be a character type, and you have to use +
instead of &
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"box2003" <box2003@.yahoo.com> wrote in message
news:%23ZIkgDgYFHA.2380@.tk2msftngp13.phx.gbl...
I have a field in a table, datatype is varchar. The field contains values
such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
want to change the field to precede all field values with 0 to yield,
01000000, 01000003, 05000001. I have attempted this using the follow but,
does not work returning error
UPDATE NumVal_1
SET NumVal = '0' & NumVal
WHERE Left(NumVal,1) > 0
Invalid operator for data type. Operator equals boolean AND, type equals
varchar
Am I even close to being on the right track?|||I had just found the & and + problem, coming in from Access I use the &.
Thank you.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OGI49IgYFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Your datatype for NumVal should be a character type, and you have to use +
> instead of &
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "box2003" <box2003@.yahoo.com> wrote in message
> news:%23ZIkgDgYFHA.2380@.tk2msftngp13.phx.gbl...
> I have a field in a table, datatype is varchar. The field contains values
> such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
> want to change the field to precede all field values with 0 to yield,
> 01000000, 01000003, 05000001. I have attempted this using the follow but,
> does not work returning error
> UPDATE NumVal_1
> SET NumVal = '0' & NumVal
> WHERE Left(NumVal,1) > 0
> Invalid operator for data type. Operator equals boolean AND, type equals
> varchar
> Am I even close to being on the right track?
>
>
such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
want to change the field to precede all field values with 0 to yield,
01000000, 01000003, 05000001. I have attempted this using the follow but,
does not work returning error
UPDATE NumVal_1
SET NumVal = '0' & NumVal
WHERE Left(NumVal,1) > 0
Invalid operator for data type. Operator equals boolean AND, type equals
varchar
Am I even close to being on the right track?Your datatype for NumVal should be a character type, and you have to use +
instead of &
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"box2003" <box2003@.yahoo.com> wrote in message
news:%23ZIkgDgYFHA.2380@.tk2msftngp13.phx.gbl...
I have a field in a table, datatype is varchar. The field contains values
such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
want to change the field to precede all field values with 0 to yield,
01000000, 01000003, 05000001. I have attempted this using the follow but,
does not work returning error
UPDATE NumVal_1
SET NumVal = '0' & NumVal
WHERE Left(NumVal,1) > 0
Invalid operator for data type. Operator equals boolean AND, type equals
varchar
Am I even close to being on the right track?|||I had just found the & and + problem, coming in from Access I use the &.
Thank you.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OGI49IgYFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Your datatype for NumVal should be a character type, and you have to use +
> instead of &
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "box2003" <box2003@.yahoo.com> wrote in message
> news:%23ZIkgDgYFHA.2380@.tk2msftngp13.phx.gbl...
> I have a field in a table, datatype is varchar. The field contains values
> such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
> want to change the field to precede all field values with 0 to yield,
> 01000000, 01000003, 05000001. I have attempted this using the follow but,
> does not work returning error
> UPDATE NumVal_1
> SET NumVal = '0' & NumVal
> WHERE Left(NumVal,1) > 0
> Invalid operator for data type. Operator equals boolean AND, type equals
> varchar
> Am I even close to being on the right track?
>
>
Add Preceeding Zero
I have a field in a table, datatype is varchar. The field contains values
such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
want to change the field to precede all field values with 0 to yield,
01000000, 01000003, 05000001. I have attempted this using the follow but,
does not work returning error
UPDATE NumVal_1
SET NumVal = '0' & NumVal
WHERE Left(NumVal,1) > 0
Invalid operator for data type. Operator equals boolean AND, type equals
varchar
Am I even close to being on the right track?
Your datatype for NumVal should be a character type, and you have to use +
instead of &
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"box2003" <box2003@.yahoo.com> wrote in message
news:%23ZIkgDgYFHA.2380@.tk2msftngp13.phx.gbl...
I have a field in a table, datatype is varchar. The field contains values
such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
want to change the field to precede all field values with 0 to yield,
01000000, 01000003, 05000001. I have attempted this using the follow but,
does not work returning error
UPDATE NumVal_1
SET NumVal = '0' & NumVal
WHERE Left(NumVal,1) > 0
Invalid operator for data type. Operator equals boolean AND, type equals
varchar
Am I even close to being on the right track?
|||I had just found the & and + problem, coming in from Access I use the &.
Thank you.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OGI49IgYFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Your datatype for NumVal should be a character type, and you have to use +
> instead of &
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "box2003" <box2003@.yahoo.com> wrote in message
> news:%23ZIkgDgYFHA.2380@.tk2msftngp13.phx.gbl...
> I have a field in a table, datatype is varchar. The field contains values
> such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
> want to change the field to precede all field values with 0 to yield,
> 01000000, 01000003, 05000001. I have attempted this using the follow but,
> does not work returning error
> UPDATE NumVal_1
> SET NumVal = '0' & NumVal
> WHERE Left(NumVal,1) > 0
> Invalid operator for data type. Operator equals boolean AND, type equals
> varchar
> Am I even close to being on the right track?
>
>
such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
want to change the field to precede all field values with 0 to yield,
01000000, 01000003, 05000001. I have attempted this using the follow but,
does not work returning error
UPDATE NumVal_1
SET NumVal = '0' & NumVal
WHERE Left(NumVal,1) > 0
Invalid operator for data type. Operator equals boolean AND, type equals
varchar
Am I even close to being on the right track?
Your datatype for NumVal should be a character type, and you have to use +
instead of &
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"box2003" <box2003@.yahoo.com> wrote in message
news:%23ZIkgDgYFHA.2380@.tk2msftngp13.phx.gbl...
I have a field in a table, datatype is varchar. The field contains values
such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
want to change the field to precede all field values with 0 to yield,
01000000, 01000003, 05000001. I have attempted this using the follow but,
does not work returning error
UPDATE NumVal_1
SET NumVal = '0' & NumVal
WHERE Left(NumVal,1) > 0
Invalid operator for data type. Operator equals boolean AND, type equals
varchar
Am I even close to being on the right track?
|||I had just found the & and + problem, coming in from Access I use the &.
Thank you.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OGI49IgYFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Your datatype for NumVal should be a character type, and you have to use +
> instead of &
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "box2003" <box2003@.yahoo.com> wrote in message
> news:%23ZIkgDgYFHA.2380@.tk2msftngp13.phx.gbl...
> I have a field in a table, datatype is varchar. The field contains values
> such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
> want to change the field to precede all field values with 0 to yield,
> 01000000, 01000003, 05000001. I have attempted this using the follow but,
> does not work returning error
> UPDATE NumVal_1
> SET NumVal = '0' & NumVal
> WHERE Left(NumVal,1) > 0
> Invalid operator for data type. Operator equals boolean AND, type equals
> varchar
> Am I even close to being on the right track?
>
>
Add Preceeding Zero
I have a field in a table, datatype is varchar. The field contains values
such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
want to change the field to precede all field values with 0 to yield,
01000000, 01000003, 05000001. I have attempted this using the follow but,
does not work returning error
UPDATE NumVal_1
SET NumVal = '0' & NumVal
WHERE Left(NumVal,1) > 0
Invalid operator for data type. Operator equals boolean AND, type equals
varchar
Am I even close to being on the right track?Your datatype for NumVal should be a character type, and you have to use +
instead of &
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"box2003" <box2003@.yahoo.com> wrote in message
news:%23ZIkgDgYFHA.2380@.tk2msftngp13.phx.gbl...
I have a field in a table, datatype is varchar. The field contains values
such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
want to change the field to precede all field values with 0 to yield,
01000000, 01000003, 05000001. I have attempted this using the follow but,
does not work returning error
UPDATE NumVal_1
SET NumVal = '0' & NumVal
WHERE Left(NumVal,1) > 0
Invalid operator for data type. Operator equals boolean AND, type equals
varchar
Am I even close to being on the right track?|||I had just found the & and + problem, coming in from Access I use the &.
Thank you.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OGI49IgYFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Your datatype for NumVal should be a character type, and you have to use +
> instead of &
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "box2003" <box2003@.yahoo.com> wrote in message
> news:%23ZIkgDgYFHA.2380@.tk2msftngp13.phx.gbl...
> I have a field in a table, datatype is varchar. The field contains values
> such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
> want to change the field to precede all field values with 0 to yield,
> 01000000, 01000003, 05000001. I have attempted this using the follow but,
> does not work returning error
> UPDATE NumVal_1
> SET NumVal = '0' & NumVal
> WHERE Left(NumVal,1) > 0
> Invalid operator for data type. Operator equals boolean AND, type equals
> varchar
> Am I even close to being on the right track?
>
>
such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
want to change the field to precede all field values with 0 to yield,
01000000, 01000003, 05000001. I have attempted this using the follow but,
does not work returning error
UPDATE NumVal_1
SET NumVal = '0' & NumVal
WHERE Left(NumVal,1) > 0
Invalid operator for data type. Operator equals boolean AND, type equals
varchar
Am I even close to being on the right track?Your datatype for NumVal should be a character type, and you have to use +
instead of &
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"box2003" <box2003@.yahoo.com> wrote in message
news:%23ZIkgDgYFHA.2380@.tk2msftngp13.phx.gbl...
I have a field in a table, datatype is varchar. The field contains values
such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
want to change the field to precede all field values with 0 to yield,
01000000, 01000003, 05000001. I have attempted this using the follow but,
does not work returning error
UPDATE NumVal_1
SET NumVal = '0' & NumVal
WHERE Left(NumVal,1) > 0
Invalid operator for data type. Operator equals boolean AND, type equals
varchar
Am I even close to being on the right track?|||I had just found the & and + problem, coming in from Access I use the &.
Thank you.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OGI49IgYFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Your datatype for NumVal should be a character type, and you have to use +
> instead of &
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "box2003" <box2003@.yahoo.com> wrote in message
> news:%23ZIkgDgYFHA.2380@.tk2msftngp13.phx.gbl...
> I have a field in a table, datatype is varchar. The field contains values
> such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
> want to change the field to precede all field values with 0 to yield,
> 01000000, 01000003, 05000001. I have attempted this using the follow but,
> does not work returning error
> UPDATE NumVal_1
> SET NumVal = '0' & NumVal
> WHERE Left(NumVal,1) > 0
> Invalid operator for data type. Operator equals boolean AND, type equals
> varchar
> Am I even close to being on the right track?
>
>
Sunday, February 19, 2012
Add Column to an existing table and mention position of the column
Hi all,
Can anybody please help me in this case.
Can anybody please help me in this case.
I have a table test(NAME,CITY)
Now i want to add a new column "ID" as primary key to table test and the position of the table should be before column "NAME".
If possible, Please suggest
Thanks
Bob
You can not do that using T-SQL. The way "Enterprise Manager" or "SSMS" does it, is creating a new table with layout you want, copying data, dropping constraint that references the old table, dropping the old table, renaming the new table and recreating all constraints and indexes.
In the relational world, the position of the column is not important.
AMB
|||The only way you can do that is to create a new table, then transfer the data from the old table to the new one, then drop the old table, and finally, rename the new table to the old table's name.|||Are their any good examples for this action, i thinking it would be easier to add new columns to the aspnet_Profile Table. To expand the user's profile data into single columns instead of one blob of data that is potentially useless. When using a SqlDataProvider for the fact the data in the row comes out in one long string of information.
Unless theirs away to break that information up into useful sections for a FormView or GridView..
Add column in middle of table
Hi all,
Is it possible to add a column to a table in a particular position? For
instance, if I had a table with columns A, B, D, E and I want to insert a
new column called 'C' but I want it to be added between columns B and D
instead of at the end of the table, is this possible? Thanks!Sure, use Enterprise Manager. It has this great technique of making a copy
of your table with the new column, copying all the data over, a fancy
rename, and don't forget moving all the constraints and permissions.
Or, stop worrying about ordinal position (there is no "end" of a table,
IMHO). Column order is irrelevant. If you have code that relies on it, fix
the code.
http://www.aspfaq.com/2478
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
> Is it possible to add a column to a table in a particular position? For
> instance, if I had a table with columns A, B, D, E and I want to insert a
> new column called 'C' but I want it to be added between columns B and D
> instead of at the end of the table, is this possible? Thanks!
Is it possible to add a column to a table in a particular position? For
instance, if I had a table with columns A, B, D, E and I want to insert a
new column called 'C' but I want it to be added between columns B and D
instead of at the end of the table, is this possible? Thanks!Sure, use Enterprise Manager. It has this great technique of making a copy
of your table with the new column, copying all the data over, a fancy
rename, and don't forget moving all the constraints and permissions.
Or, stop worrying about ordinal position (there is no "end" of a table,
IMHO). Column order is irrelevant. If you have code that relies on it, fix
the code.
http://www.aspfaq.com/2478
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
> Is it possible to add a column to a table in a particular position? For
> instance, if I had a table with columns A, B, D, E and I want to insert a
> new column called 'C' but I want it to be added between columns B and D
> instead of at the end of the table, is this possible? Thanks!
Thursday, February 9, 2012
actual calc result not the same as Reporting Service calc result
I am using a table and at the grouping level, I am dividing the sum of one
field (oputut format 1 decimal position) by the sum of another field (output
format 1 decimal position). The output appears as follows .6 / 1.4 . If you
do the math on a calculator, the result wound be arounded .43. However
Visual Studio is calculating .45 (output format 2 decimals) . I suspect this
is because the detail (4 decimal positions) is summarized and then rounded.
My calculation is sum(a) / sum(b) with an output format mask of 2 decimal
places.
Is there any way to reference the actual data from the report summary line
(the .6 and the 1.4), or Can I construct my equation in such a way that when
the data on the report is calcualted manually it agrees with the results from
Visual Studio? The users will not trust the data if they manually try to
verify the report and it doesn't match.On Mar 23, 12:55 pm, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> I am using a table and at the grouping level, I am dividing the sum of one
> field (oputut format 1 decimal position) by the sum of another field (output
> format 1 decimal position). The output appears as follows .6 / 1.4 . If you
> do the math on a calculator, the result wound be arounded .43. However
> Visual Studio is calculating .45 (output format 2 decimals) . I suspect this
> is because the detail (4 decimal positions) is summarized and then rounded.
> My calculation is sum(a) / sum(b) with an output format mask of 2 decimal
> places.
> Is there any way to reference the actual data from the report summary line
> (the .6 and the 1.4), or Can I construct my equation in such a way that when
> the data on the report is calcualted manually it agrees with the results from
> Visual Studio? The users will not trust the data if they manually try to
> verify the report and it doesn't match.
If I'm understanding you correctly, you can either do the calculation
as part of the stored procedure/query that is sourcing the report
(i.e., CAST(SUM(A)/SUM(B) AS DECIMAL(10,2)) or similar) or call a
stored procedure that includes this type of calculation in the VS
application. Also, you could use something like this as part of a
stored procedure: CAST(SUM(CAST(A AS DECIMAL(10,2)))/SUM(CAST(B AS
DECIMAL(10,2))) AS DECIMAL(20,2)). Hope this is helpful.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Enrique,
What does "CAST" mean, and is there somewhere that I can get a list of all
the function codes (ie. cast, sum, etc.) and their descriptions?
Thank you,
Patti
"EMartinez" wrote:
> On Mar 23, 12:55 pm, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > I am using a table and at the grouping level, I am dividing the sum of one
> > field (oputut format 1 decimal position) by the sum of another field (output
> > format 1 decimal position). The output appears as follows .6 / 1.4 . If you
> > do the math on a calculator, the result wound be arounded .43. However
> > Visual Studio is calculating .45 (output format 2 decimals) . I suspect this
> > is because the detail (4 decimal positions) is summarized and then rounded.
> > My calculation is sum(a) / sum(b) with an output format mask of 2 decimal
> > places.
> >
> > Is there any way to reference the actual data from the report summary line
> > (the .6 and the 1.4), or Can I construct my equation in such a way that when
> > the data on the report is calcualted manually it agrees with the results from
> > Visual Studio? The users will not trust the data if they manually try to
> > verify the report and it doesn't match.
> If I'm understanding you correctly, you can either do the calculation
> as part of the stored procedure/query that is sourcing the report
> (i.e., CAST(SUM(A)/SUM(B) AS DECIMAL(10,2)) or similar) or call a
> stored procedure that includes this type of calculation in the VS
> application. Also, you could use something like this as part of a
> stored procedure: CAST(SUM(CAST(A AS DECIMAL(10,2)))/SUM(CAST(B AS
> DECIMAL(10,2))) AS DECIMAL(20,2)). Hope this is helpful.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||Enrique,
Here is a simplified sample of my expression statement with the "CAST" added:
=CAST(Sum( Fields!Measures_M1Vol_N.Value) as decimal(13,1) ) /
CAST(Sum( Fields!Measures_M2BudVol_N.Value) as decimal(13,1) )
When I go to preview my report is says that "Name CAST is not declared".
I did the calculations manually and my probelm is defitely the fact that the
calcualations are performed on unrounded data, therefore giving me a larger
result. If possible I need to be able to round the numerator and the
denominator before the division actually takes place. Is there a different
way to format the expression so it will do this?
Thank you,
Patti
"ppbedz" wrote:
> I am using a table and at the grouping level, I am dividing the sum of one
> field (oputut format 1 decimal position) by the sum of another field (output
> format 1 decimal position). The output appears as follows .6 / 1.4 . If you
> do the math on a calculator, the result wound be arounded .43. However
> Visual Studio is calculating .45 (output format 2 decimals) . I suspect this
> is because the detail (4 decimal positions) is summarized and then rounded.
> My calculation is sum(a) / sum(b) with an output format mask of 2 decimal
> places.
> Is there any way to reference the actual data from the report summary line
> (the .6 and the 1.4), or Can I construct my equation in such a way that when
> the data on the report is calcualted manually it agrees with the results from
> Visual Studio? The users will not trust the data if they manually try to
> verify the report and it doesn't match.
field (oputut format 1 decimal position) by the sum of another field (output
format 1 decimal position). The output appears as follows .6 / 1.4 . If you
do the math on a calculator, the result wound be arounded .43. However
Visual Studio is calculating .45 (output format 2 decimals) . I suspect this
is because the detail (4 decimal positions) is summarized and then rounded.
My calculation is sum(a) / sum(b) with an output format mask of 2 decimal
places.
Is there any way to reference the actual data from the report summary line
(the .6 and the 1.4), or Can I construct my equation in such a way that when
the data on the report is calcualted manually it agrees with the results from
Visual Studio? The users will not trust the data if they manually try to
verify the report and it doesn't match.On Mar 23, 12:55 pm, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> I am using a table and at the grouping level, I am dividing the sum of one
> field (oputut format 1 decimal position) by the sum of another field (output
> format 1 decimal position). The output appears as follows .6 / 1.4 . If you
> do the math on a calculator, the result wound be arounded .43. However
> Visual Studio is calculating .45 (output format 2 decimals) . I suspect this
> is because the detail (4 decimal positions) is summarized and then rounded.
> My calculation is sum(a) / sum(b) with an output format mask of 2 decimal
> places.
> Is there any way to reference the actual data from the report summary line
> (the .6 and the 1.4), or Can I construct my equation in such a way that when
> the data on the report is calcualted manually it agrees with the results from
> Visual Studio? The users will not trust the data if they manually try to
> verify the report and it doesn't match.
If I'm understanding you correctly, you can either do the calculation
as part of the stored procedure/query that is sourcing the report
(i.e., CAST(SUM(A)/SUM(B) AS DECIMAL(10,2)) or similar) or call a
stored procedure that includes this type of calculation in the VS
application. Also, you could use something like this as part of a
stored procedure: CAST(SUM(CAST(A AS DECIMAL(10,2)))/SUM(CAST(B AS
DECIMAL(10,2))) AS DECIMAL(20,2)). Hope this is helpful.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Enrique,
What does "CAST" mean, and is there somewhere that I can get a list of all
the function codes (ie. cast, sum, etc.) and their descriptions?
Thank you,
Patti
"EMartinez" wrote:
> On Mar 23, 12:55 pm, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > I am using a table and at the grouping level, I am dividing the sum of one
> > field (oputut format 1 decimal position) by the sum of another field (output
> > format 1 decimal position). The output appears as follows .6 / 1.4 . If you
> > do the math on a calculator, the result wound be arounded .43. However
> > Visual Studio is calculating .45 (output format 2 decimals) . I suspect this
> > is because the detail (4 decimal positions) is summarized and then rounded.
> > My calculation is sum(a) / sum(b) with an output format mask of 2 decimal
> > places.
> >
> > Is there any way to reference the actual data from the report summary line
> > (the .6 and the 1.4), or Can I construct my equation in such a way that when
> > the data on the report is calcualted manually it agrees with the results from
> > Visual Studio? The users will not trust the data if they manually try to
> > verify the report and it doesn't match.
> If I'm understanding you correctly, you can either do the calculation
> as part of the stored procedure/query that is sourcing the report
> (i.e., CAST(SUM(A)/SUM(B) AS DECIMAL(10,2)) or similar) or call a
> stored procedure that includes this type of calculation in the VS
> application. Also, you could use something like this as part of a
> stored procedure: CAST(SUM(CAST(A AS DECIMAL(10,2)))/SUM(CAST(B AS
> DECIMAL(10,2))) AS DECIMAL(20,2)). Hope this is helpful.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||Enrique,
Here is a simplified sample of my expression statement with the "CAST" added:
=CAST(Sum( Fields!Measures_M1Vol_N.Value) as decimal(13,1) ) /
CAST(Sum( Fields!Measures_M2BudVol_N.Value) as decimal(13,1) )
When I go to preview my report is says that "Name CAST is not declared".
I did the calculations manually and my probelm is defitely the fact that the
calcualations are performed on unrounded data, therefore giving me a larger
result. If possible I need to be able to round the numerator and the
denominator before the division actually takes place. Is there a different
way to format the expression so it will do this?
Thank you,
Patti
"ppbedz" wrote:
> I am using a table and at the grouping level, I am dividing the sum of one
> field (oputut format 1 decimal position) by the sum of another field (output
> format 1 decimal position). The output appears as follows .6 / 1.4 . If you
> do the math on a calculator, the result wound be arounded .43. However
> Visual Studio is calculating .45 (output format 2 decimals) . I suspect this
> is because the detail (4 decimal positions) is summarized and then rounded.
> My calculation is sum(a) / sum(b) with an output format mask of 2 decimal
> places.
> Is there any way to reference the actual data from the report summary line
> (the .6 and the 1.4), or Can I construct my equation in such a way that when
> the data on the report is calcualted manually it agrees with the results from
> Visual Studio? The users will not trust the data if they manually try to
> verify the report and it doesn't match.
Subscribe to:
Posts (Atom)