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.
| Author |
Topic |
|
bvdhanasekaran
Starting Member
2 Posts |
 Posted - 2008-03-11 : 07:22:30
|
| Can anyone help me how to add a new column between a column in sql server 2005i have did this one in SQL Server 2000 like below.1. Add a new column using ALTER statement2. EXEC sp_configure 'show advanced options',1 GO RECONFIGURE GO EXEC sp_configure 'allow updates',1 GO RECONFIGURE WITH OVERRIDE GO3.Change the colid in syscolumns table using UPDATE Statement- But its not working in SQL Server 2005...it throws an adhoc error for system catalog table |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-03-11 : 07:25:56
|
| Why should you want this? the position of column in table is not at a problem while retrieving data. You can always specify the retrival script to retrieve the data in the order you want.And if you are so particular to do this, you may try updating the column ORDINAL_VALUE of catalog view INFORMATION_SCHEMA.COLUMNS rather than syscolumn table. |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2008-03-11 : 08:42:32
|
| Do you need this because you are using "SELECT * " , you might better off stating the exact columns in the SELECT statemet , such as "select col1, col2 " etcJack Vamvas--------------------Search IT jobs from multiple sources- http://www.ITjobfeed.com |
|
|
|
bvdhanasekaran
Starting Member
2 Posts |
Posted - 2008-03-13 : 09:12:11
|
| Hi,We have maintained some standard while creating table design. each and every table having Last_Acceed_By and Last_Accessed_Date at the end of table. This is for an identification purpose when any action takes place at that time of Insert/Update. Now i ad a new column through an alter querya against a table...new field will add that end. To keep our table design i did colid in syscolumn table in SQL Server 2000 after the addition of a column...But am not able to update the colid (column_id in SQL 2005) field....This is a problem |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-03-13 : 12:28:28
|
| But cant you access them as last column of SELECT statements ? Do you really need to tamper with ORDINAL_VALUE of columns? |
|
|
|
tprupsis
Yak Posting Veteran
88 Posts |
Posted - 2008-03-13 : 13:53:03
|
| There's no technical reason for having to do this, but sometimes it is useful or just preferable to have columns in specific order. The easiest way to do it is to make the design changes in SSMS. If you want the code, just script the result before you save changes. But you'll notice that the script actually creates a temp table, inserts all the data to it, drops the old table, and then renames the temp table. I'm guessing if that's the way MS built it, there probably isn't a simple way to change the column id. |
|
|
|
|
|
|
|
|
Add a new column between columns in a table