在SQL Server中的列之间插入一列 [英] insert a column between columns in SQL Server

问题描述

如何在SQL Server中的两列之间添加列



这是我的sql查询但是没有工作它显示一些错误

How to add the column in between two columns in SQL server

This is my sql query but is not working it display some errors

alter table [login] add mobileno bigint not null AFTER UserName

推荐答案

AFAIK，您无法插入新列使用查询或脚本在两个现有的之间。使用GUI，您可以通过选择表设计中的列行并选择插入列选项。



主要的是为什么这一点很重要？ SQL表中特定列的顺序没有区别。您可以在基于表格设计的查询和视图中始终获得所需的结果。

AFAIK, you cannot insert a new column between two existing one using a query or a script. Using GUI, you can by selecting the column row in table-design and choosing insert a column option.

The main thing is why does that matter at all? There is no difference in which order a particular column lies in SQL table. You can always have desired result in your queries and views designed based on the tables.

您无法在不创建包含所需位置列的新表的情况下插入列，并且在删除旧表并重命名新表之前复制信息。



想要按特定顺序排列列的唯一原因是因为您使用的是SELECT * FROM语法：出于多种原因，这仍然是一种不好的做法。首先，因为它会导致程序行为不同或者在外部更改数据库时失败，其次因为返回您不感兴趣的列会严重浪费带宽。始终明确列出要检索的列或影响因为这可能意味着问题在继续损坏数据库中的大量数据之前就已被捕获。

You can't insert columns, without creating a new table including the column in the position you want, and copying the info over, before deleting the old table and renaming the new.

The only reason for wanting columns in a specific order is because you are using SELECT * FROM syntax: which is a bad practice anyway for a number of reasons. Firstly, because it causes your program to behave differently or fail if the database is changed externally, and secondly because it is a serious waste of bandwidth to return columns you are not interested in. Always explicitly list the columns you want to retrieve, or affect as this can mean that a problem is picked up before it goes on to damage significant amounts of data in your database.

你不能以编程方式（以安全的方式）执行此操作创建一个新表。没有其他方法可以在现有列之间的SQL Server表中插入列 - 您需要构建临时表并重建旧表。



提交重新排序时企业管理器的作用是创建新表，移动数据然后删除旧表并将新表重命名为现有名称。



如果您希望您的列按特定顺序/分组而不改变其物理顺序，您可以创建一个可以是您想要的视图。

You can not do this programatically (in a safe way that is) without creating a new table. There isn't another way to insert a column in a SQL Server table "in between" existing columns - you need to build a temp table and rebuild the old table.

What Enterprise Manager does when you commit a reordering is to create a new table, move the data and then delete the old table and rename the new table to the existing name.

If you want your columns in a particular order/grouping without altering their physical order, you can create a view which can be whatever you desire.

这篇关于在SQL Server中的列之间插入一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！