INSERTING COLUMNS
-
Hi Guys.
I'm new in this forum and I have a doubt about ALTER TABLE. Is it posible to spcify to T-SQL that I want to add a column in a table after or before one another?. I heard that MySQL has an AFTER keyword. I don't know if there is an equivalent in T-SQL.
Thank you.
-
Nope... new columns always get added to the "end" of the table if you use just an ALTER.
However, if you go into Enterprise Manager to design the table (add the new column(s)), it will write the code necessary to put the column the spot you desire. Do be advised that it makes a copy of the table so you need enough disk space to hold the new table and, if it has a lot of rows, you need to be patient. You should make the change ONLY when no one else is accessing the table...
Personally, it shouldn't matter where the column goes on an ordinal basis. Maybe if it's a very wide table and you just want to keep related columns together for ease of viewing during analysis...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Jeff:
Thank you for your help. It is not a big deal for me either, is just that I want to group the columns together according to its content, as you said in your last paragraph. So I'm going to follow your advice about enterprise manager and see the result and I hope there will be a feature like this in future versions of SQL Server because I prefer to write sentences rather than visual assitants. Thak you very much.
-
Not a problem... thanks for the feedback.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
>>
Thank you for your help. It is not a big deal for me either, is just that I want to group the columns together according to its content, as you said in your last paragraph. So I'm going to follow your advice about enterprise manager and see the result and I hope there will be a feature like this in future versions of SQL Server because I prefer to write sentences rather than visual assitants.
<<
Then do it in two steps.
SELECT [fieldslist] FROM OriginalTable into a temporary table, where you create a literal placeholder for the column you want to add, at the appropriate position in the list. Then drop the original table and SELECT * FROM TempTable INTO OriginalTable.
To make this generic code, you need to create the delimited list of columns yourself, pass the name of the column AFTER which you want to insert the new column as well as its type-description. But you can certainly do it without the visual tools if you want. It may look inefficient but probably something similar is happening under the visual tools anyway.
>L<
-
Actually, you'll need quite a few other scripts if there are indexes, foreign keys, and triggers. That's why I use EM to take care of it... it "automagically" does all of that.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply