ALTER TABLE to add a column between other columns

I need to use ALTER TABLE in order to add a column between the columns of a
specific table. The table is in an production environment.
I think to use a unique Transact-SQL statement that allows to alter the
previous colums and add my column in the right position inside structure
table.
I have used this statement:
ALTER TABLE mytable
ALTER COLUMN mypreviouscolumn typecolumn(precision, scale)
ADD COLUMN mycolumn typecolumn(precision, scale)
but I have generated a syntax error.
How can I solve this issue?
Many thanks
This is not possible with ALTER TABLE.
It really shouldn't be necessary, anyway. The order that the columns are
returned when you SELECT * is not necessarily the order they are physically
stored on the data pages. If you want to return columns in a particular
order, you can SELECT with a column list, or create a view of the table with
the columns in the order you want them.
The graphical tools make you think you can add a column in a particular
position, but they do this by completely recreating a new table. That can
take a long time on a big table.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:246FA243-3EAA-40C5-8AC7-9462DD64B8AB@.microsoft.com...
>I need to use ALTER TABLE in order to add a column between the columns of a
> specific table. The table is in an production environment.
> I think to use a unique Transact-SQL statement that allows to alter the
> previous colums and add my column in the right position inside structure
> table.
> I have used this statement:
> ALTER TABLE mytable
> ALTER COLUMN mypreviouscolumn typecolumn(precision, scale)
> ADD COLUMN mycolumn typecolumn(precision, scale)
> but I have generated a syntax error.
> How can I solve this issue?
> Many thanks
>

ALTER TABLE to add a column between other columns

I need to use ALTER TABLE in order to add a column between the columns of a
specific table. The table is in an production environment.
I think to use a unique Transact-SQL statement that allows to alter the
previous colums and add my column in the right position inside structure
table.
I have used this statement:
ALTER TABLE mytable
ALTER COLUMN mypreviouscolumn typecolumn(precision, scale)
ADD COLUMN mycolumn typecolumn(precision, scale)
but I have generated a syntax error.
How can I solve this issue?
Many thanksThis is not possible with ALTER TABLE.
It really shouldn't be necessary, anyway. The order that the columns are
returned when you SELECT * is not necessarily the order they are physically
stored on the data pages. If you want to return columns in a particular
order, you can SELECT with a column list, or create a view of the table with
the columns in the order you want them.
The graphical tools make you think you can add a column in a particular
position, but they do this by completely recreating a new table. That can
take a long time on a big table.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:246FA243-3EAA-40C5-8AC7-9462DD64B8AB@.microsoft.com...
>I need to use ALTER TABLE in order to add a column between the columns of a
> specific table. The table is in an production environment.
> I think to use a unique Transact-SQL statement that allows to alter the
> previous colums and add my column in the right position inside structure
> table.
> I have used this statement:
> ALTER TABLE mytable
> ALTER COLUMN mypreviouscolumn typecolumn(precision, scale)
> ADD COLUMN mycolumn typecolumn(precision, scale)
> but I have generated a syntax error.
> How can I solve this issue?
> Many thanks
>

ALTER TABLE to add a column between other columns

I need to use ALTER TABLE in order to add a column between the columns of a
specific table. The table is in an production environment.
I think to use a unique Transact-SQL statement that allows to alter the
previous colums and add my column in the right position inside structure
table.
I have used this statement:
ALTER TABLE mytable
ALTER COLUMN mypreviouscolumn typecolumn(precision, scale)
ADD COLUMN mycolumn typecolumn(precision, scale)
but I have generated a syntax error.
How can I solve this issue?
Many thanksThis is not possible with ALTER TABLE.
It really shouldn't be necessary, anyway. The order that the columns are
returned when you SELECT * is not necessarily the order they are physically
stored on the data pages. If you want to return columns in a particular
order, you can SELECT with a column list, or create a view of the table with
the columns in the order you want them.
The graphical tools make you think you can add a column in a particular
position, but they do this by completely recreating a new table. That can
take a long time on a big table.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:246FA243-3EAA-40C5-8AC7-9462DD64B8AB@.microsoft.com...
>I need to use ALTER TABLE in order to add a column between the columns of a
> specific table. The table is in an production environment.
> I think to use a unique Transact-SQL statement that allows to alter the
> previous colums and add my column in the right position inside structure
> table.
> I have used this statement:
> ALTER TABLE mytable
> ALTER COLUMN mypreviouscolumn typecolumn(precision, scale)
> ADD COLUMN mycolumn typecolumn(precision, scale)
> but I have generated a syntax error.
> How can I solve this issue?
> Many thanks
>

Alter table and column order

Is it possible to add a column to a table using the "alter table"
statement and specify where in the sequence of columns the new column
sits. If not is there any way to alter the order of columns using TSQL
rather than Enterprise Manager / Design Table.

TIA

Laurence BreezeNo and no.

The physical column order should only be significant when you use SELECT * -
and you shouldn't use SELECT * in production code. List the columns in your
SELECT statements in whatever order you want them. Alternatively, create a
view over the table with the columns in the required order.

Your other option is to create a new table, populate it from the original
table and then rename it. This is what Enterprise Manager does behind the
scenes.

--
David Portas
SQL Server MVP
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> No and no.
> The physical column order should only be significant when you use SELECT
> * - and you shouldn't use SELECT * in production code. List the columns
> in your SELECT statements in whatever order you want them.

However, in support or debug situations, SELECT * is very convenient to
use. And in this case, I find it important that columns are in some
reasonable order. And historic order is rarely reasonable.

Furthermore, if you want to move data between databases, it is far
simpler if columns are in the same order in both databases, as this
makes bulk-copying easier.

One should also not ignore the documenation aspect of it. When you read
the documentation of a 50-column table, do you prefer to have the columns
in historic order, or do you prefer some logic order with the primary
key first, and related columns close to each other.

So, while there is no support to insert a column in the middle other
than creating a new table and move data over, it is certainly a valid
question. Column order does matter!

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Alphabetical Order - Eliminating the "THE"

Hi,
This may seem like a simple problem, and I'm somewhat embarrassed that
I've been developing for 7 years and haven't been asked to deal with
this - but when you are ordering a list alphabetically, HOW do you
factor out the preceeding "The" in your list items when you do your
ordering. For example in a list of movies such as:
Into the Blue
Madagascar
Spaceballs
The 40-year old virgin
The Exorcism of Emily Rose
doing a standard Order By on the title field would render the list like
that, but according to the rules of english this is an incorrect
ordering, since The 40-year old virgin and The King and I are both
ordered at the end and should instead be ordered at the top. How do
you get this to happen? Here is some generic sample code that would
order the list the way it appears above. How can I modify the code to
order it according to the rules of English.
SELECT movie_title, producer, cost from tblMovie ORDER BY movie_title
Thanks in advance.You will need to order on an expression. For example, try the following:
select
*
from
movies
order by
case when left(title,4) = 'The ' then substring(title,5,255) else Title
end
<rwilson290@.hotmail.com> wrote in message
news:1129302125.799285.113600@.g49g2000cwa.googlegroups.com...
> Hi,
> This may seem like a simple problem, and I'm somewhat embarrassed that
> I've been developing for 7 years and haven't been asked to deal with
> this - but when you are ordering a list alphabetically, HOW do you
> factor out the preceeding "The" in your list items when you do your
> ordering. For example in a list of movies such as:
> Into the Blue
> Madagascar
> Spaceballs
> The 40-year old virgin
> The Exorcism of Emily Rose
>
> doing a standard Order By on the title field would render the list like
> that, but according to the rules of english this is an incorrect
> ordering, since The 40-year old virgin and The King and I are both
> ordered at the end and should instead be ordered at the top. How do
> you get this to happen? Here is some generic sample code that would
> order the list the way it appears above. How can I modify the code to
> order it according to the rules of English.
> SELECT movie_title, producer, cost from tblMovie ORDER BY movie_title
> Thanks in advance.
>|||I don't really think that it's an English rule for ordering, but I could be
wrong.
For "special" ordering like this, I would add an "order_title" column.
The titles would look like this in the column:
Into the Blue
Madagascar
Spaceballs
40-year old virgin (or Forty-year old virgin)
Exorcism of Emily Rose
Establish the rules and when inserting, format the title and insert the
formatted title in that column .
I'm sure that you'll find other exceptions like titles that start with "A
...".
So your query becomes:
SELECT movie_title, producer, cost from tblMovie ORDER BY order_title
You could, like JT has suggested, incorporate this into the ORDER BY clause
or write a function to do this.
But, depending on the size of this table and other rules may may be added,
this could really slow down your query.
It may be better to take the time when inserted to get it right.
<rwilson290@.hotmail.com> wrote in message
news:1129302125.799285.113600@.g49g2000cwa.googlegroups.com...
> Hi,
> This may seem like a simple problem, and I'm somewhat embarrassed that
> I've been developing for 7 years and haven't been asked to deal with
> this - but when you are ordering a list alphabetically, HOW do you
> factor out the preceeding "The" in your list items when you do your
> ordering. For example in a list of movies such as:
> Into the Blue
> Madagascar
> Spaceballs
> The 40-year old virgin
> The Exorcism of Emily Rose
>
> doing a standard Order By on the title field would render the list like
> that, but according to the rules of english this is an incorrect
> ordering, since The 40-year old virgin and The King and I are both
> ordered at the end and should instead be ordered at the top. How do
> you get this to happen? Here is some generic sample code that would
> order the list the way it appears above. How can I modify the code to
> order it according to the rules of English.
> SELECT movie_title, producer, cost from tblMovie ORDER BY movie_title
> Thanks in advance.
>

Alpha Order in Management Studio

I know this isn't the right section, but they offered me no alternative.

I'm in Management Studio and am perusing the columns of a table, but they aren't in alphabetical order. How do I sort them by alphabetical order? There doesn't seem to be an obvious way to do this.

Thanks.

Moving to Transact-SQL forum for starters. This isn't an SSIS issue.

I do know that the columns appear in order of creation. Perhaps writing a query (and hence the move to Transact-SQL forum) would suit your needs.|||Click on the little SQL button in the toolbar, and add a ORDER BY clause to the statement.|||

Agreed Phil. Just use a query as:

select *

from sys.columns

where object_id = object_id('tableName')

order by name

The query could be expanded to include datatypes and such if you want, but if you are just trying to get the columns in the UI sorted, it doesn't work this way. I would suggest you file a suggestion here:

https://connect.microsoft.com/SQLServer/feedback/

Allow user to choose grouping order?

I have a report which is working off a dataset with 5 columns. Date, Type, Color, Value1, Value2
We would like to set up a report such the user viewing the report could drag and drop column headers and set up grouping in whatever order they want. What would be the best way to do this? Is it even possible, or do we have to create a separate report for each combination?

For example, One report just groups by Date and Type. They might also want to group by Type then Date. Other times we want Date, Type, then color.

I used to run the reports in a third party grid which supported this, but since it was all client side, it was very slow for large datasets.

You can change the grouping through report parameters. Chris has an example of this in his blog:

http://blogs.msdn.com/chrishays/archive/2004/07/15/DynamicGrouping.aspx

Allow reference to dynamically-created DataColumn from reportHelp?

Hi everyone,

I currently have a strongly-typed dataset that, in code, will expand tables according to relationships in that dataset in order to bind it to my ReportViewer. My problem now is whenever I try to run it, I get an error looking like the following:

An error occurred during local report processing.
The definition of the report 'Main Report' is invalid.
The Value expression for the textbox `textbox15` refers to the field `Parent_FullName`. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data scope.

The reason I'm dynamically creating this dataset on the fly is because I couldn't find how to do a one-to-one relationship (bind a column in one report table to a column in a different dataset table than everything else on that report table). I realize the problem is that in the report's "DataSets" node, it doesn't include a definition of these generated columns... but is there any way around this?

Time is of the essence, but I'd appreciate any help at all!

TIA! =)

Hello,

You may want to look into using calculated fields. These allow you to use expressions as the source of data fields and are used just like regular fields.

For more information about calculated fields:
http://msdn2.microsoft.com/en-us/library/ms156295.aspx

Also, you should find more helpful information if you search for "calculated fields" in this forum and the web.

Ian|||

Hi Ian,

That's exactly what I'm doing. When the dataset is passed in to the form, I have the form automatically expand on its existing relationships and add calculated fields to the datatables at runtime. I can't do this at design-time because I'm using this one form to eventually run 100's of reports--all using various strongly-typed datasets, etc. I think I found the problem, in that it holds the dataset information in the .rdlc file itself and expects ALL the fields to be there. Unless someone knows a way to override it -requiring- all fields to be there(?), I assume I'll have to parse the XML in the .rdlc file at runtime also and add in the XML for all the calculated fields I add as it adds them to the datatables themselves. Any better suggestions are appreciated though. =)

Thanks!

|||Okay, dynamically adding the columns worked for my immediate needs, but that soon passed. :-\ Is there ANY way at all to link different datatables in the same dataset together? I need to take a value from a field in the current scope (i.e. "FamilyID" in the dsMember_Entity dataset) and link it up with a field in another dataset ("FamilyName" in dsMember_Family where dsMember_Entity.FamilyID = dsMember_Family.FamilyID). There are a LOT of fields that will be worked this way, so are there any functions that would allow me to do this using calculated fields? If not, are there any suggestions how one might go about this?|||There are no built-in mechanisims in RS for joining multiple datasets together. Is it possible to join the entity and family table together in the SQL query, so that all fields are avaliable? If not, then you may want to look at writing a custom data extenstion that joins the data tables into one dataset. Or you may want to look into using custom code to return the appropriate value from one dataset given a value from another.

Let me know if you want more information about any of these topics.

Ian|||

Hi Ian,

The tables are joined in to one dataset. We're using strongly-typed datasets, and have all of the relationships already set up. Is there any way to use the existing relationships to get the values I'd like?

|||With the built-in data extensions, you can only access one table per dataset, so the tables need to be joined into one table based on the relationships already set up to be used in a data region. (This is without the use of custom lookup code and secondary datasets.) Did you join the tables on FamilyID, so each row would have the rows from the dsMember_Family table joined with the appropriate rows of the dsMember_Entity table? If so, the fields should be available in the same fields collection.

Also, how are you creating and accessing the strongly typed dataset?

Ian

|||

The dataset has multiple tables, as some of the results are many-to-one we can't just lop all the data onto one table. Foreign key relationships exist in that strongly-typed dataset, however.

We have an internal utility that creates the strongly-typed dataset from a schema it gets from stored procedures. I.e., a "GetEntityData" stored procedure will return several different resultsets--our schema generator checks out these results and writes a strongly-typed dataset based on it... then we just add them into our project and can do any changes we require.

The datasets are accessed in the WinForm's code; we want to use one form for reports instead of one form per report, so we just pass in the dataset we need directly to the form and it handles the binding.