用于更改列位置的SQL查询 [英] sql query for changing position of columns

问题描述

我用col-A col-B col-C col-D创建了表格。

我觉得需要再添加一列表col-X。

所以我使用SQL语句添加了col：

 更改 表 table_name  ADD  col-X  int   null ; 

并且它添加到最后位置，即在col-D之后，

但是我想将col-X列添加到col-A的位置，即第一个位置。



Plz帮助我，应该触发什么查询来改变SQL中列的位置。







Thxnx

解决方案

嘿Mike Meinz是对的，没有任何QUERY将列插入特定位置，它将总是在你的列集上添加到End。

我们不应该受到SQL中列的索引的限制。

但是，这是一个技巧实现你的要求for

  BEGIN  
 test = *＃创建 表 TABLE1_temp  as   select  COL1，null :: int  as  COL2，COL3 < span class =code-keyword>来自 TABLE1; 
  SELECT  
 test = *＃ drop  表 TABLE1; 
  DROP  表 
 test = *＃ alter   table  TABLE1_temp将重命名为 TABLE1; 
  ALTER   TABLE  
 test = *＃提交; 
  COMMIT  
 test =＃选择 * 来自 TABLE1; 

希望它有用！

你需要使用自定义顺序重新创建 table_name 到 new_name 。然后将数据从 table_name 复制到 new_name ，删除 table_name 和将 new_name 更改为 table_name 。这就是全部！



示例：

 创建 表 new_name（ColX  INT   IDENTITY  （ 1 ， 1 ），ColA  INT  ，ColB  VARCHAR （ 50 ），ColC  VARCHAR （ 255 ））
 
  INSERT   INTO  new_name（ColA，ColB，ColC）
  SELECT  ColA，ColB，ColC 
  FROM  table_name 
 
  DROP  table_name 
 
 sp_rename new_name，table_name 

更多： sp_rename（T- SQL） [<小时ef =http://msdn.microsoft.com/en-us/library/ms188351.aspxtarget =_ blanktitle =New Window> ^ ]

< blockquote>我向你保证，你对这些领域的顺序没有区别。你应该总是创建明确引用字段的语句，而不是依赖它们的任何假定顺序。

如果您仍想重新排序列，则必须使用SQL Management Studio： http://msdn.microsoft.com/en-us/library/aa337556.aspx [ ^ ]。

也读这个：http://blog.sqlauthority.com/2008/04/08/sql-server-change-order-of-column-in -database-tables / [ ^ ]

正如您将注意到的，没有人鼓励您尝试在t-sql中执行此操作。它可以是don，但不应该这样做。所以忘记这种方法。

Hi,
I have created table with columns col-A col-B col-C col-D.
I felt need of adding one more column to that table col-X.
So I added that col using SQL statement:

Alter Table table_name ADD col-X int null;

and it added to last position i.e after col-D,
But I want to add that column col-X to position of col-A i.e to first position.

Plz help me with , what query should be fired to change the position of column in SQL.



Thxnx

解决方案

Hey Mike Meinz is right that there is no any QUERY to insert the column into a specific position, it will ALWAYS be added to the End on your column set.
and we should not be limited by the index of the column in SQL :)
But , here is a trick to achieve what you are asking for

BEGIN
test=*# create table TABLE1_temp as select COL1, null::int as COL2, COL3 from TABLE1;
SELECT
test=*# drop table TABLE1;
DROP TABLE
test=*# alter table TABLE1_temp rename to TABLE1;
ALTER TABLE
test=*# commit;
COMMIT
test=# select * from TABLE1;

Hope it's useful !

You need to re-create table_name to new_name with custom ordering. Then copy data from table_name to new_name, drop table_name and change new_name to table_name. That's all!

Example:

CREATE TABLE new_name (ColX INT IDENTITY(1,1), ColA INT, ColB VARCHAR(50), ColC VARCHAR(255))

INSERT INTO new_name (ColA, ColB, ColC)
SELECT ColA, ColB, ColC
FROM table_name

DROP table_name

sp_rename new_name, table_name

More: sp_rename (T-SQL)[^]

I assure you, it makes no difference in which order you have those fields. You should always make statements, that explicitly refer to fields, and do't rely on any assumed order of them.
If you still want to reorder columns, you have to use SQL Management Studio: http://msdn.microsoft.com/en-us/library/aa337556.aspx[^].
Read this too: http://blog.sqlauthority.com/2008/04/08/sql-server-change-order-of-column-in-database-tables/[^]
As you will notice, nobody is encouraging you in trying to do this in t-sql. It can be don, but should not be done. So forget this approach.

这篇关于用于更改列位置的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！