Adding a column

Question

Post reply

Adding a column

Chandhini

Ten Centuries

Points: 1243
More actions
April 13, 2010 at 6:49 am

#97269

Hi,
I have a table called Customer. It has Cust_Id, Cust_Name, Cust_SSN, Cust_Address. There are some records in table. I would like to insert one more column Cust_DOB after Cust_Name (after second column) through T-SQL. not at the design mode using SSMS. I have tried the below SQL Query. but i m getting an error.
ALTER TABLE Customer ADD Cust_DOB datetime AFTER Cust_Name.
Can anyone suggest me regarding this issue.
Thanks in advance.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

BrainDonor SSCoach Points: 19254 More actions · Answer 1

What is the error message?

And is there a reason that you're trying to place the new column anywhere other than on the end?

BrainDonor.

Steve Hall
Linkedin
Blog Site

Chandhini Ten Centuries Points: 1243 More actions · Answer 2

M getting incorrect syntax error. and m asked to insert this column in between existing columns.

WayneS SSC Guru Points: 95460 More actions · Answer 3

You cannot add a column to a table at a specified column position; adding a column adds it to the end of the table.

If you really need this column in this position (there's really no reason to require this, but ...), then you will need to:

rename this table.

build new table with columns in the desired order.

insert into new table select from old table.

rebuild all indexes / defaults / constraints

IMO, quite a lot of needless work to put a column in a specified position. Especially when you can select the columns in the desired order (instead of select *, do select column1, column5, column3, column2, column4). The front-end application will never know the difference.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

BrainDonor SSCoach Points: 19254 More actions · Answer 4

This kind of thing has been discussed a few times in the past. The basic answer is no, without more work than it is probably worth.

http://www.sqlservercentral.com/Forums/Topic476826-338-1.aspx

http://www.sqlservercentral.com/Forums/Topic643902-360-1.aspx

BrainDonor.

Steve Hall
Linkedin
Blog Site

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

@Chandhini . if you post the syntax of your ALTER statement , we can try to give the cause of its failure

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 6

WayneS (4/13/2010)
If you really need this column in this position (there's really no reason to require this, but ...), then you will need to:
rename this table.
...
IMO, quite a lot of needless work to put a column in a specified position. Especially when you can select the columns in the desired order (instead of select *, do select column1, column5, column3, column2, column4). The front-end application will never know the difference.

UNLESS....the front end is dependent on the ordinal positions of the columns. The dev team where I recently worked used the SSMS UI to insert some new columns (in the 'middle' of the list of columns) into a table on the dev box, then used their fancy, home-grown code generator to write the C# classes for CRUD work. Come time to roll out to the QA system, I used SQL Compare (from RedGate) to generate the update script -- it didn't bother checking order, it just created an update script that appended the new columns to the 'end' of the column list. I applied the script and all should be well, right?

Unfortunately, I then learned that the code generator wrote code dependent on the ordinal position of the columns in the table. So, when we started testing, NOTHING worked, since the columns on the QA box were in a different order than those on the DEV box.

Sometimes, it DOES make a difference.

Rob Schripsema
Propack, Inc.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Chandhini (4/13/2010)
Hi,
I have a table called Customer. It has Cust_Id, Cust_Name, Cust_SSN, Cust_Address. There are some records in table. I would like to insert one more column Cust_DOB after Cust_Name (after second column) through T-SQL. not at the design mode using SSMS. I have tried the below SQL Query. but i m getting an error.
ALTER TABLE Customer ADD Cust_DOB datetime AFTER Cust_Name.
Can anyone suggest me regarding this issue.
Thanks in advance.

WHY don't you want to use SSMS? Since it does this for you automatically, this seems like an unusual restriction.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

vaibhav.tiwari SSCommitted Points: 1976 More actions · Answer 8

This is the only option if you dont want to use enterprise manager

-- creating the new table with the structure required

CREATE TABLE Emp_temp(

emp_num INT NOT NULL PRIMARY KEY,

first_name CHAR(30) NOT NULL,

middle_name CHAR(30) NULL,

last_name CHAR(30) NOT NULL

);

GO

-- copying the data from Employees table into Emp_temp table

INSERT INTO Emp_temp(emp_num, first_name, last_name)

SELECT * FROM Employees;

GO

-- deleting the Employees table

DROP TABLE Employees;

GO

-- Renaming Emp_temp table into Employees table

EXEC sp_rename 'Emp_temp', 'Employees';

GO

Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together

Chandhini Ten Centuries Points: 1243 More actions · Answer 9

Thanks a lot for all your valuable inputs.

Now, creating a table with new structure, dumping the data and renaming the table is the only option!!!.