December 21, 2008 at 7:52 am
Hello,
I need to add some columns to several existing tables.
Some have over three hundred thousand records and I can't use the management studio.
However, I noticed that I can add the columns to the tables by TSQL. The only problem is that when I used the ALTER TABLE to add add a column, it puts the fields at the end of the table.
I need the new columns to be in column position 2-6.
Is it possible to add columns to an existing table using TSQL but designate the position of the columns in the table?
Thanks,
Tony
Things will work out. Get back up, change some parameters and recode.
December 21, 2008 at 7:59 am
Not possible without recreating the table completely, which is what Management Studio does.
Why do you need the columns in a specific position? Technically, column order has no meaning in SQL. It's easy enough to change the select statements to put the columns in whatever position is required.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 21, 2008 at 10:43 am
And why can't you use SSMS for this?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 21, 2008 at 12:13 pm
Tried to add new column and then tried to reshuffle it to different order in the existing table, gave me warning that it would need to drop and recreate table. So I guess your best bet would be to drop and recreate table.
December 21, 2008 at 12:21 pm
sqlizer (12/21/2008)
Tried to add new column and then tried to reshuffle it to different order in the existing table, gave me warning that it would need to drop and recreate table. So I guess your best bet would be to drop and recreate table.
That's your only bet. But I would recommend that you use SSMS to do it, because it is much less likely to make a mistake.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 21, 2008 at 1:45 pm
I fully agree with Gail that column order should not matter in SQL and generally the answer to changing column order is just to reorder the output in the select statement (or if you want to use * often, you can reorder it one time in a view and then select from the view).
But, while there is no command to move them easily, it can be done. It is not gracefully. If you use alter table to add a column, it will always add it to the end. So, if you must, you can add the new columns to the end. Then, you can rename every column in between where they are and where you want the to be with an _old extension. Then add a copy of each of the columns you just renamed with their proper name to the end, in the right order. Then copy the currently held data in the _old columns to the newly created ones, and finally drop all the columns with the _old extension.
I absolutely do not recommend doing this, but it works if you must.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
December 21, 2008 at 6:59 pm
And, if you decide to follow 'timothywiseman' solution - don't forget to remove any indexes and recreate them on the new columns, as well as the foreign key references (if any).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 22, 2008 at 2:17 am
You can recreate the table with a select statement.
SELECT AreaID, RegionID, '' as NewField,Area, AreaCode
Into Areas_Modified
FROM Areas
But then you will need to use your alter statement to modify that column to your required data type because on creation it will be defaulted to varchar(1).
after that don't forget to drop your old table so that you can replace with the new one.
December 22, 2008 at 10:43 am
-> WebTechie38
You still have not indicated WHY you would bother with the order of the columns in the first place. Is this really the NEED or the need confused with a WAY of doing it? You just might be surprised if, based on enough information, someone might offer you a far better solution.
I hope it is not because some bozo programmer has embedded exrpessions such as "Fields[0]" in an application. (One miscreant actually did this and I was the one who had to deal with this later).
December 22, 2008 at 11:01 am
disable any constraints,etc and rename the table. Create the new table with the column order and then pump the data across. But why, as pointed out already it doesnt matter in SQL
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 22, 2008 at 12:28 pm
Thanks everyone!
And yes I get it, the column order shouldn't matter.
I was just thinking of nice and neat tables all matching with same order. But as Gail said it doesn't matter.
Lesson learned and notated.
My resolution:
1) copy to new table.
2) delete records
3) create my columns (one being entity) and putting the columns in my desired order
4) used SSIS to pump data from one table to the other
I did this before Gail's response. Again, I know now I didn't need to worry about the column order.
Thanks again.
Things will work out. Get back up, change some parameters and recode.
December 22, 2008 at 12:30 pm
By the way, what is SSMS?
I know DTS and its big brother SSIS. But what is SSMS?
Thanks,
Tony
Things will work out. Get back up, change some parameters and recode.
December 22, 2008 at 12:36 pm
WebTechie38 (12/22/2008)
By the way, what is SSMS?I know DTS and its big brother SSIS. But what is SSMS?
Thanks,
Tony
SSMS is SQL Server Management Studio, it replaced The Enterprise Manager that was in SQL Server 2000 the way SSIS replaced DTS.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
December 22, 2008 at 1:59 pm
Hi,
I couldn't use SSMS because it was giving me an error message. I tried but since the tables had a lot of data, I couldn't just move the columns around.
Thanks.
Things will work out. Get back up, change some parameters and recode.
December 22, 2008 at 2:06 pm
WebTechie38 (12/22/2008)
Hi,I couldn't use SSMS because it was giving me an error message. I tried but since the tables had a lot of data, I couldn't just move the columns around.
Thanks.
There is a way around the issues - if you really, really, really, .... need to do this. I do not suggest that you do this, in fact - I recommend against doing this.
You can uncheck the option 'Prevent saving changes that require table re-creation' in Options under the Table and Database Designers section. By unchecking this item - when you modify the column order, SSMS will generate the code to copy the table, insert the data into the new table, drop the old table and rename the new table.
Again, I do not recommend doing this - because there really is no reason to.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply