InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 01:17:11 GMT20http://www.sqlservercentral.com/Forums/Topic461499-5-1.aspx[quote][b]chanchal_sheik (3/25/2013)[/b][hr]SELECT colname1, colname2, colname5, colname4, colname3 into NewTableName from OldTableName**this will create at NewTable with columns in the above sequence.[/quote]Correct. But although it will correctly copy the IDENTITY property and NULLability constraints if done on the same instance, it will not create some of the more important parts like the PK, AKs, FKs, Indexes, Triggers, etc, etc.Mon, 25 Mar 2013 19:47:37 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic461499-5-1.aspx[quote][b]GilaMonster (3/25/2013)[/b][hr]Please note: 5 year old thread.[/quote]And they left out the important parts, to boot! :-)Mon, 25 Mar 2013 19:44:29 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic461499-5-1.aspxPlease note: 5 year old thread.Mon, 25 Mar 2013 10:45:34 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic461499-5-1.aspxSELECT colname1, colname2, colname5, colname4, colname3 into NewTableName from OldTableName**this will create at NewTable with columns in the above sequence.Mon, 25 Mar 2013 10:15:58 GMTchanchal_sheikhttp://www.sqlservercentral.com/Forums/Topic461499-5-1.aspx[quote][b]Ian Yates (2/28/2008)[/b][hr]I second Andras - I'm wary of software that absolutely requires the columns be in exact order due to their use of select * in queries and then accessing the columns by index 0, 1, 2 rather than by name. It's a bad practice along the lines of tricks to get "order by" in views. It creates instant doubt in the ability of the database designer to have made it efficient (no server-side cursors, etc) and you have to double-check everything they say when you report problems with the app... :w00t: :DThe easy way to get the columns nicely ordered is to use the GUI tools to do the work for you. Use the tool to insert the column and then either save it and let it do the work or save a script. It will take care of dropping and recreating all indices, foreign key constraints, etc as you could well overlook something or make a mistake.I guess it's nice from a database diagram perspective to have the columns nicely ordered and, depending on your reporting tools and how end-users use them it's also nice to have the columns in a particular order. A purist would say that the end user, if they must use a reporting tool, should only be accessing views anyway in which case you can change the columns by simply changing the view. If this is your situation then consider using views.[/quote]I third that... and if you just gotta have it that way, don't reinvent the wheel... use EM to make the change and copy the script it produces if you need to keep it.Thu, 28 Feb 2008 11:59:54 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic461499-5-1.aspx[quote][b]niranjankumar_k (2/28/2008)[/b][hr]Without droping this table , anyother way just to mention in right order for new column ?[/quote]If you don't want to write all script of what John had said. Then open design table window for that table and then create a new column in the place you want to. Then before saving the changes just click the create script option (a page like icon) on the window. you will get the script. Copy it and execute it on QA. But make sure you are not saving changes in design table window.If the table size is big then I will take backup of table and then execute this script. You have trouble some time, in second but last step it truncates data from the original table and tab_locks sometimes are so long that it fails the insert operation from temp table and you are tend to loose all your data.Thu, 28 Feb 2008 11:29:25 GMTSanjayAttrayhttp://www.sqlservercentral.com/Forums/Topic461499-5-1.aspxI second Andras - I'm wary of software that absolutely requires the columns be in exact order due to their use of select * in queries and then accessing the columns by index 0, 1, 2 rather than by name. It's a bad practice along the lines of tricks to get "order by" in views. It creates instant doubt in the ability of the database designer to have made it efficient (no server-side cursors, etc) and you have to double-check everything they say when you report problems with the app... :w00t: :DThe easy way to get the columns nicely ordered is to use the GUI tools to do the work for you. Use the tool to insert the column and then either save it and let it do the work or save a script. It will take care of dropping and recreating all indices, foreign key constraints, etc as you could well overlook something or make a mistake.I guess it's nice from a database diagram perspective to have the columns nicely ordered and, depending on your reporting tools and how end-users use them it's also nice to have the columns in a particular order. A purist would say that the end user, if they must use a reporting tool, should only be accessing views anyway in which case you can change the columns by simply changing the view. If this is your situation then consider using views.Thu, 28 Feb 2008 05:05:44 GMTIan Yateshttp://www.sqlservercentral.com/Forums/Topic461499-5-1.aspx[quote][b]niranjankumar_k (2/28/2008)[/b][hr]Without droping this table , anyother way just to mention in right order for new column ?[/quote]Unfortunately John is right, rebuilding your table is the only alternative. However, I have always been wondering why column order is important in tables? Indexes of course are different :). Columns should be looked up by names, and not by sequence numbers. Even if you use select * (bad practice), lookup by name should work. If you have a very large number of columns then of course it may make sense to group them logically. Could you describe your reasons for forcing a particular column order?Regards, AndrasThu, 28 Feb 2008 02:52:41 GMTAndras Belokosztolszkihttp://www.sqlservercentral.com/Forums/Topic461499-5-1.aspxtest=# create table TABLE1 (COL1 int, COL3 int);CREATE TABLEtest=*# insert into TABLE1 values (1,3);INSERT 0 1test=*# commit;COMMITtest=# begin;BEGINtest=*# create table TABLE1_temp as select COL1, null::int as COL2, COL3 from TABLE1;SELECTtest=*# drop table TABLE1;DROP TABLEtest=*# alter table TABLE1_temp rename to TABLE1;ALTER TABLEtest=*# commit;COMMITtest=# select * from TABLE1;Thu, 28 Feb 2008 02:51:33 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic461499-5-1.aspxWithout droping this table , anyother way just to mention in right order for new column ?Thu, 28 Feb 2008 02:33:50 GMTniranjankumar_khttp://www.sqlservercentral.com/Forums/Topic461499-5-1.aspxThe ALTER TABLE ADD COLUMN command will add the new column to the end of the table. If you need to change the position of a column within a table, you have to re-create the table. Here's what you'll need to do:1. Create new table with correct column order.2. INSERT into new table from old table.3. Drop FK constraints from old table.4. Drop old table5. rename new table to old name6. Recreate FKs.Thu, 28 Feb 2008 02:23:21 GMTJohn Rowanhttp://www.sqlservercentral.com/Forums/Topic461499-5-1.aspxTable contain 6 column in order like ABDEFGNeed to addd one more column "C" after B column. (in between B and D ). how to write alter script for this.Thu, 28 Feb 2008 02:16:15 GMTniranjankumar_k