Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
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 21:02pm
sahmed4562
Rate this: bad
good
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
  Permalink  
Comments
sahmed4 at 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 at 12-Feb-13 3:48am
   
Good answer, my 5!
See my answer.
Rate this: bad
good
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: 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
  Permalink  
Comments
Mehdi Gholam at 12-Feb-13 4:28am
   
5'ed
Maciej Los at 12-Feb-13 5:15am
   
Thank you ;)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

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.
  Permalink  
v2

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

  Print Answers RSS
0 OriginalGriff 330
1 Sergey Alexandrovich Kryukov 279
2 BillWoodruff 245
3 Afzaal Ahmad Zeeshan 215
4 CPallini 205
0 OriginalGriff 5,635
1 DamithSL 4,496
2 Maciej Los 3,942
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,180


Advertise | Privacy | Mobile
Web03 | 2.8.141216.1 | Last Updated 12 Feb 2013
Copyright © CodeProject, 1999-2014
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