在SQL Server 2005中现有列之后添加新列的SQL查询

I need a SQL query which add a new column after an existing column, so the column will be added in a specific order.

我需要一个SQL查询，它在一个已有的列之后添加一个新的列，因此该列将按特定的顺序添加。

Please suggest me if any ALTER query which do that.

如果有任何改变，请建议我。

7 个解决方案

#1

ALTER TABLE mytable
ADD COLUMN  new_column <type>
AFTER       existing_column

MsSQL (AFAIK) does not allow you to alter the table and add a column after a specific column. Your best bet is using Sql Server Management Studio, or play around with either dropping and re-adding the table, or creating a new table and moving the data over manually. neither are very graceful.

MsSQL (AFAIK)不允许您更改表并在特定列之后添加列。最好的方法是使用Sql Server Management Studio，或者尝试删除和重新添加表，或者创建一个新表并手动移动数据。也很优雅。

#2

ALTER won't do it because column order does not matter for storage or querying

ALTER不会这么做，因为列顺序对存储和查询都不重要

If SQL Server, you'd have to use the SSMS Table Designer to arrange your columns, which can then generate a script which drops and recreates the table

如果是SQL Server，则必须使用SSMS表设计器来安排列，然后可以生成一个脚本，该脚本删除并重新创建表

Edit Jun 2013

编辑2013年6月

Cross link to my answer here: Performance / Space implications when ordering SQL Server columns?

我的回答是:在订购SQL Server列时，性能/空间的影响?

#3

It's possible.

这是有可能的。

First, just add each column the usual way (as the last column).

首先，按照通常的方式(作为最后一列)添加每一列。

Secondly, in SQL Server Management Studio Get into Tools => Options.

其次，在SQL Server Management Studio中，进入Tools =>选项。

Under 'Designers' Tab => 'Table and Database Designers' menu, uncheck the option 'Prevent saving changes that require table re-creation'.

在“设计器”选项卡=>“表和数据库设计器”菜单下，取消“防止需要重新创建表的更改保存”选项。

Afterwards, right click on your table and choose 'Design'. In 'Design' mode just drag the columns to order them.

然后，右键单击您的表，选择“Design”。在“设计”模式下，只需拖动列来排序。

Don't forget to save.

别忘了保存。

#4

If you want to alter order for columns in Sql server, There is no direct way to do this in SQL Server currently.

如果您想更改Sql server中列的顺序，目前在Sql server中没有直接的方法。

Have a look at http://blog.sqlauthority.com/2008/04/08/sql-server-change-order-of-column-in-database-tables/

查看http://blog.sqlauthority.com/2008/04/08/sql-server-change-order-of- in-database-tables/

You can change order while edit design for table.

您可以在编辑表格时更改订单。

#5

First add the new column to the old table through SSMStudio. Go to the database >> table >> columns. Right click on columns and choose new column. Follow the wizard. Then create the new table with the columns ordered as desired as follows:

首先，通过SSMStudio将新列添加到旧表中。转到数据库>>表>>列。右键单击列并选择new column。遵循向导。然后创建新表，列的顺序如下所示:

select * into my_new_table from (
select old_col1, my_new_col, old_col2, old_col3
from my_old_table 
) as A
;

Then rename the tables as desired through SSMStudio. Go to the database >> table >> choose rename.

然后通过SSMStudio按需要重命名表。转到数据库>>表>>选择重命名。

#6

For MS SQL Server, ALTER TABLE tablename ADD columnname INT will work. It is of course, not possible to add a column after a specific column as in MySQL using the after command.

对于MS SQL Server，可以使用ALTER TABLE tablename添加columnname INT。当然，在特定的列之后添加列是不可能的，就像在MySQL中使用after命令一样。

#7

-2

It is a bad idea to select * from anything, period. This is why SSMS adds every field name, even if there are hundreds, instead of select *. It is extremely inefficient regardless of how large the table is. If you don't know what the fields are, its still more efficient to pull them out of the INFORMATION_SCHEMA database than it is to select *.

在任何时候选择*是一个坏主意。这就是为什么SSMS会添加每个字段名，即使有数百个，而不是select *。不管桌子有多大，它的效率都非常低。如果您不知道字段是什么，那么将它们从INFORMATION_SCHEMA数据库中取出比选择*更有效。

A better query would be:

更好的查询是:

SELECT 
 COLUMN_NAME, 
 Case 
  When DATA_TYPE In ('varchar', 'char', 'nchar', 'nvarchar', 'binary') 
  Then convert(varchar(MAX), CHARACTER_MAXIMUM_LENGTH)  
  When DATA_TYPE In ('numeric', 'int', 'smallint', 'bigint', 'tinyint') 
  Then convert(varchar(MAX), NUMERIC_PRECISION) 
  When DATA_TYPE = 'bit' 
  Then convert(varchar(MAX), 1)
  When DATA_TYPE IN ('decimal', 'float') 
  Then convert(varchar(MAX), Concat(Concat(NUMERIC_PRECISION, ', '), NUMERIC_SCALE)) 
  When DATA_TYPE IN ('date', 'datetime', 'smalldatetime', 'time', 'timestamp') 
  Then '' 
 End As DATALEN, 
 DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS 
Where 
 TABLE_NAME = ''

#1

MySQL:

ALTER TABLE mytable
ADD COLUMN  new_column <type>
AFTER       existing_column

#2