How do I add a column after another column within MS SQL by using SQL query?
TempTable ID int, Type nvarchar(20), Active bit
NewTable ID int, Type nvarchar(20), Description text, Active bit
That is what I want, how do I do that
3
-
1Column order is irrelevant to SQL. You can define column order in SELECT, INSERT or UPDATE statements.OMG Ponies– OMG Ponies2010-11-03 03:43:22 +00:00Commented Nov 3, 2010 at 3:43
-
1Yeah I know, but I just like things structured a certain waymattgcon– mattgcon2010-11-03 06:13:02 +00:00Commented Nov 3, 2010 at 6:13
-
1since column order really isn't relevant, there's no such feature in SQL Server / T-SQL. You need to either use the interactive table designer (which will re-create the entire table in the process), or you just have to let it be.marc_s– marc_s2010-11-03 06:30:40 +00:00Commented Nov 3, 2010 at 6:30
Add a comment
|
3 Answers
Add a column to the end of the table:
ALTER TABLE myTable ADD myNewColumn VARCHAR(255);
Add a column to the start of the table:
ALTER TABLE myTable ADD myNewColumn VARCHAR(255) FIRST;
Add a column after an existing column:
ALTER TABLE myTable ADD myNewColumn VARCHAR(255) AFTER myOtherColumn;
For additional options, see MySQL's ALTER TABLE documentation.
10 Comments
mattgcon
this is MS SQL not MySQL unfortunately
Hamish
In that case, I believe you have to recreate the entire table. Why does column order matter?
mattgcon
I am just really picky like that, and prefer tables to have a certain order. Ok I will just have to recreate it
Hamish
Heh, fair enough. Maybe add the column, then add copies of the columns that you want to be after the added column, update the column values, drop old columns and rename the new. A pain, but necessary.
Usman
I tried it on MYSQL and it worked for me. Server: MySQL Version: 5.5.29 Engine InnoDB.
|
It depends on what database you are using. In MySQL, you would use the "ALTER TABLE" syntax. I don't remember exactly how, but it would go something like this if you wanted to add a column called 'newcol' that was a 200 character varchar:
ALTER TABLE example ADD newCol VARCHAR(200) AFTER otherCol;
Comments
In a Firebird database the AFTER myOtherColumn does not work but you can try re-positioning the column using:
ALTER TABLE name ALTER column POSITION new_position
I guess it may work in other cases as well.
