Add Column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Add Column

Author

Topic

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-01-25 : 02:49:01

Hi,

I have one table with two cols, i.e. SrNo, Name

noew i wants to add one more column to this table. It should be:
SrNo, RollNo, Name. It is possible from table designing. how it can be achieve through DDL.

thanks in advance,

Mahesh

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-25 : 02:51:13

ALTER TABLE <YourColumnNameHere> ADD RollNo INT

More information exists in Books Online, if you have forgotten about the excellent help file.

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-25 : 02:51:14

Why is the ordering of the column a concern to you ?

you can always specify the order using select statement

select SrNo, RolNo, Name from table

instead of just

select * from table

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-25 : 02:52:34

anyway, it is bad idea to use select *. You will have problem when you add new column to the table

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-25 : 02:56:18

Even if you are allowed to do this,

select * From tblsales

update information_schema.columns
set ordinal_position = 13 - ordinal_position 
where ordinal_position in (6, 7)
	and table_name = 'tblsales'

select * From tblsales

the change is not reflected.

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-25 : 03:01:39

if you really want to do this, use Enterprise Manager (EM), table design to do it.

But EM will basically create a new table with the column ordering that you want, copy the data over to the new table, drop the old table and rename new table to old table. The foreign key constraints will also be drop and re-created.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-01-25 : 04:00:36

quote:
Originally posted by Peso

Even if you are allowed to do this,
select * From tblsales

update information_schema.columns
set ordinal_position = 13 - ordinal_position
where ordinal_position in (6, 7)
and table_name = 'tblsales'

select * From tblsales
the change is not reflected.

Peter Larsson
Helsingborg, Sweden

Is it safe to play around with the Systems tables, since i had face this problem many times but then i just recreated the table with the specific ordinal position.. and then migrated the data to it

Chirag

http://chirikworld.blogspot.com/

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-01-25 : 04:09:09

thanks all,

i wanted to know, is it possible to add column inbetween table through DDL.

regards,

Mahesh

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-01-25 : 04:28:48

No its not possible.. since when ever you add the column using Alter Statment then its added at the end of the table..

The other way is the add via EM and then generate the script .. but the script is quite lengthy enough.. since its creates the temp table and then dump the records of the existing table into it and then drop the existings table and then rename the temp table...

Chirag

http://chirikworld.blogspot.com/

rizwan
Starting Member

9 Posts

Posted - 2007-02-06 : 10:52:53

chiragkhabaria, you said
"The other way is the add via EM and then generate the script .. but the script is quite lengthy enough.. since its creates the temp table and then dump the records of the existing table into it and then drop the existings table and then rename the temp table..."

Can you please tell how can we generate script after adding column through EM ?

Thanks

--
Riz
www.PDFonFLY.com - Online HTML to PDF conversion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources