如何在现有列T-SQL语句的添加中添加列 [英] How to Add Columns in betwwen of Existing Columns T-SQL Statements

问题描述

亲爱的用户，

如何使用SQL Server 2005中的T-SQL语句在现有列之间添加新列。

例如，在我现有的表中，我有列Id，Name，Age

我需要使用transact SQL语句在Name和Age列之间插入Password列。

Dear Users,
How to add a new column between the existing columns using T-SQL Statements in SQL Server 2005.
For Example in my existing table i have the columns Id,Name,Age
I need to insert Password column between Name and Age column using transact SQL statement.

推荐答案

你不要不需要，你总是可以按照你想要的顺序进行选择：

You don''t need to, you can always do a select in the order you want:

select ID, name, password, age from tablename

Mehdi Gholam答案很好，但是你需要一些explonation。



你可以这样做，你可以在现有的coulmns之间插入列，但它需要重新创建表。默认情况下，新列将作为列集合中的最后一列插入。正如Mehdi Gholam所写，您可以随时按照自己的意愿选择数据（列）。

更多信息： http://msdn.microsoft.com/en-us/library/ms190273（v = sql.90）.aspx [ ^ ]



如果您仍想在现有列之间插入新列，我建议您执行以下步骤：

1）创建新表 NewTable 包含所需的列

2）将数据从 ExistingTable 复制到 NewTable 默认 [密码] 。

3）删除 ExistingTable

Mehdi Gholam is answer is good, but you need some explonation.

You can do it and you can insert column between existing coulmns, but it needs to re-create table. By default new column is inserted as a last column in the column collection. As Mehdi Gholam wrote, you can always select data (columns) in order you want.
More at: http://msdn.microsoft.com/en-us/library/ms190273(v=sql.90).aspx[^]

If you still want to insert new column between existing columns, i recommend you to do these steps:
1) create new table NewTable with desired columns
2) copy data from ExistingTable into NewTable with default [Password].
3) remove ExistingTable

一开始...

In the beginning ...

--CREATE SCHEMA [cpqa]		

CREATE TABLE[cpqaAnswers].[cpqa].[tblSFThree] (
	[intId][int],
		[strName][nvarchar](81),
			[intAge][int]
			)
SELECT * FROM  [cpqaAnswers].[cpqa].[tblSFThree]

没有数据，只有一张桌子...

No data, just a table ...

DECLARE @ph [nvarchar](20)
SET @ph = ''nomanshallpass''

SELECT [intId], [strName], @ph As [strPass], [intAge] FROM  [cpqaAnswers].[cpqa].[tblSFThree]

虽然没有名为[strPass]的表字段，但在TSQL中，有可能在运行时将这样的事物命名为使用DECLARE语句的变量。

While there''s no table field called [strPass], it''s possible, in TSQL, to name such a thing on-the-fly as a variable using a DECLARE statement.

CREATE TABLE [cpqaAnswers].[cpqa].[tblSFFour] (
	[intId][int],
		[strName][nvarchar](81),
			[strPass][nvarchar](20),
				[intAge][int]
				)

SELECT [intId], [strName], [strPass], [intAge] FROM  [cpqaAnswers].[cpqa].[tblSFFour]

但要创建另一个字段，需要更多输入。现在有一个正式的占位符

But to create another field, some more typing is required. Now there''s a formal placeholder

SELECT * FROM [cpqaAnswers].[cpqa].[tblSFFour]

当我说只有一个表时，我的意思是不允许任何NULL，所以将NULL视为一切，因此没有。

When I say there''s just a table, I mean "no NULLs are allowed" so think of NULL as everything and therefore nothing.

这篇关于如何在现有列T-SQL语句的添加中添加列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！