sql加列语句,如何在现有列T-SQL语句的添加中添加列

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 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]

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]

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