Click here to Skip to main content
12,999,845 members (48,700 online)
Rate this:
Please Sign up or sign in to vote.
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.
Posted 11-Feb-13 20:02pm
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

You don't need to, you can always do a select in the order you want:
select ID, name, password, age from tablename
sahmed4 12-Feb-13 3:47am
Hi mehdi.. i hope u haven't understand my Question i need to insert a new column between the existing column using T-SQL statements
Maciej Los 12-Feb-13 3:48am
Good answer, my 5!
See my answer.
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

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:[^]

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
Mehdi Gholam 12-Feb-13 4:28am
Maciej Los 12-Feb-13 5:15am
Thank you ;)
Rate this: bad
Please Sign up or sign in to vote.

Solution 3

In the beginning ...
--CREATE SCHEMA [cpqa]		
CREATE TABLE[cpqaAnswers].[cpqa].[tblSFThree] (
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] (
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.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170624.1 | Last Updated 12 Feb 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100