如何在sql server中的表中的特定位置添加新列 [英] how to add the new column in specific location in a table in sql server
问题描述
我的表A有5列：
TableA
-
名称
电话
电子邮件
地址
我想在电话和电子邮件之间添加一个新列（移动）：
TableA
-
名称
电话
手机
电子邮件
地址
如果我使用
ALTER TABLE TableA
ADD COLUMN Mobile INT NOT NULL
移动列添加到表格的末尾。
有没有办法实现这一点而不删除表并将数据移动到新表？
I have Table A with 5 columns:
TableA
--
Name
Tel
Email
Address
I want to add a new column (mobile) in between Tel & Email:
TableA
--
Name
Tel
Mobile
Email
Address
If I use
ALTER TABLE TableA
ADD COLUMN Mobile INT NOT NULL
the mobile column is added to the end of the table.
Is there a way to achieve this without dropping the table and moving data to a new table?
推荐答案
该表是关系模型中的一组集合。由于行之间没有明确的顺序，因此列之间也没有明确的顺序。但是您可以在投影期间通过指定所需的列而不是使用*重新排序它们。
但当然在您的情况下，RDBMS，SQL服务器仍在使用某些排序。您无法在特定位置添加字段，但可以更改字段顺序。让我引用这里的答案： http://www.sqlteam.com/forums/topic.asp？ TOPIC_ID = 58912 [ ^ ]
The table is a set of sets in relational model. As there is no explicit order between the rows, there is no explicit order between the columns either. But you can reorder them during projection by specifying the columns you need instead of using*.
But of course the RDBMS, SQL server in your case is still using some ordering. You can't really add the field at specific position, but you can change field order. Let me quote the answer from here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58912[^]
Information_Schema.Columns表有一个ORDINAL_POSITION字段。是否可以通过ALTER TABLE简单地添加所需的列然后更改Information_Schema.Columns中表的ORDINAL_POSITION？
我没试过这个，但似乎以下方法可能有效。此示例假定有一个包含2列的表。基本上添加新列，将其分配到位置1，然后将旧列分配到位置2.
- 添加列
如果不是EXISTS（SELECT * FROM [information_schema]。[columns] WHERE table_name ='TargetTable'AND table_schema ='dbo'AND column_name ='TheNewColumn'）
BEGIN
ALTER TABLE [dbo]。[TargetTable]
ADD TheNewColumn int
END
- 移动新列的顺序位置到顶部。
UPDATE [information_schema]。[columns]
SET ORDINAL_POSITION = 1
WHERE table_name ='TargetTable'AND table_schema ='dbo'AND column_name ='TheNewColumn'
- 将旧列的序号位置移到底部。
UPDATE [information_schema ]。[columns]
SET ORDINAL_POSITION = 2
WHERE table_name ='TargetTable'AND table_schema ='dbo'AND column_name ='TheOldColumn'
一些SQL大师现在可能会尖叫，这会搞砸系统级别的东西。这就是我在这里发布的原因。
The Information_Schema.Columns table has an ORDINAL_POSITION field. Is it possible to simply add the desired columns via ALTER TABLE then change the ORDINAL_POSITION for the table in Information_Schema.Columns?
I haven't tried this, but it seems the following approach may work. This example assumes there is a table with 2 columns. Basically add the new column, assign it to position 1, then assign the old column to position 2.
-- Add the column
IF NOT EXISTS (SELECT * FROM [information_schema].[columns] WHERE table_name = 'TargetTable' AND table_schema = 'dbo' AND column_name = 'TheNewColumn')
BEGIN
ALTER TABLE [dbo].[TargetTable]
ADD TheNewColumn int
END
-- Move the new column's ordinal position to the top.
UPDATE [information_schema].[columns]
SET ORDINAL_POSITION = 1
WHERE table_name = 'TargetTable' AND table_schema = 'dbo' AND column_name = 'TheNewColumn'
-- Move the old column's ordinal position to the bottom.
UPDATE [information_schema].[columns]
SET ORDINAL_POSITION = 2
WHERE table_name = 'TargetTable' AND table_schema = 'dbo' AND column_name = 'TheOldColumn'
Some SQL guru is probably screaming right now that this would screw up something at a system level. That's why I'm posting it here.
Altrough MSDN [ ^ ]说，这不能从T-SQL完成，而是从SQL Management Studio完成。顺便说一下，那个选项没错？
Altrough MSDN[^] says, that this can't be done from T-SQL, but from SQL Management Studio. By the way, wha't wrong with that option?
这篇关于如何在sql server中的表中的特定位置添加新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
