How to add column in specific position
-
Hi
How to add column in specific position without using Enterprise manager.is it possible through stored procedure or any other method.if we use alter statement the column will be added in the last.but i dont want like this. i want like this .for example here are some columns
id
address
phone
now i want to add Name after the id column.
regards
Nazri
-
Adding a column using alter table will always put the column at the end.
Enterprise manager completely recreates the table when you modify the structure, that's why it can add a column anywhere.
Why does the position of the column matter. When you select, you can order the columns anyway you please.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Yes. If you want this to be done from the script you need to get the use of a temporary table and recreate the table.
My Blog:
http://dineshasanka.spaces.live.com/ -
The only reason I can think of is when people wants to write queires as 'SELECT * FROM....' which should be avoided in the first place.
The recommendation is to always write out the columns to be selected, so the request for placing new columns in any specific order should in 99.99% of the cases be a non-issue.
/Kenneth
-
It is actually a flaw of SQL (ANSI-SQL and all flavors of it) to even put any meaning to column order.
-
Well, generally I agree, and I never write SELECT * in any SQL that is used repeatedly. But from a practical viewpoint, it is fine that the structure of a table has defined column order and that I can use *. It helps to get orientation in a new table, or in one that I've not worked with for some time. At such occasions, I write SELECT TOP 10 * FROM... to see all columns with their contents and to find out, which of them I want/have to use right now. Here it helps a lot, if the columns are ordered in some way, not with first_name as col1 and last_name as col48. So, I can understand why people would like to have their columns ordered and not add new columns at the end - although I probably wouldn't trouble myself, if the table is already in production.
-
Chris, an ANSI 'flaw', I think not. Column order does not matter at all in the DDL. The 'flaw' is in the interpretation. Below I've excerpted a post of mine from another thread discussing this same subject:
Where the column is in the table creation DDL are located visually really does not matter. The columns for the actual row itself are stored in the following (very over-simplified format):
- page header
- fixed length column map
- fixed length columns (not nullable)
- variable length column map
- variable length columns (all varchar and nullable columns)
So if you want th emost efficient design, use fixed length non-nullable columns and make your key/index columns the first columns in the DDL (an added bonus is you get 'update in place'). If this sounds 'old school', it is !. Some things just do not change - like basic DBMS page formats ( MS/SQL, Sybase,DB2) at least until we hit the next generation of datbases.
Here's ther original thread: http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=223922#bm224593
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Rudy, I did not say anything about how the rows are stored. I said that SQL puts meaning to the order in which you specify columns for a table. For instance, you create a table FOOBAR (foo int, bar int) and use a SELECT * FROM FOOBAR statement to return data from it. Now, if you change the table to FOOBAR (bar int, foo int) your SELECT * statement now has a different meaning.
The most complete treatment of this that I have found is Chris Date's paper A Sweet Disorder.
Because SQL has a left-to-right order of columns we see these questions on how to insert a column at some position in a table.
-
The answer to your question, Nazri, is that Enterprise Manager (2000) and SQL Server Management Studio (2005) actually go ahead and create temp tables with the desired column order (the one that you can rearrange visually from within the studio) and then drop the original table and rename the temp table to achieve this purpose. It's not very sexy, but that's the way it's done.
This is what your query would look like using the column names you provided as an example:
CREATE TABLE dbo.TEMP_myTable (id, Name, address, phone) ON [PRIMARY]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[myTable]') AND type IN (N'U'))
DROP TABLE [dbo].[myTable]
GO
EXECUTE sp_rename N'dbo.TEMP_myTable', N'myTable','OBJECT'
GO
---------------
Just change the any instances of "myTable" above with the actual name of your table and you should be good to go. Of course, you won't want to forget to add column constraints (int, varchar, primary key, etc.) after each column name too.
-
Hi friend,,
add a new column between table not posible in oracle,sq, but it's posible in mysql,,
use following query to add a new column between table
alter table table_name add new_table_name data_type [FIRST|AFTER] existing_table name;
by
chennai-2
-
For all those balking about column order not mattering in a table, I absolutely agree when it comes to programming.
But, for troubleshooting, it's sometimes very helpful and can save some real time to have columns display in SSMS (either in the Explorer or from a TOP 100 * look-see) in an "expected" order especially if there are a decent number of tables with many columns.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
MICHAEL DEPAULA (9/9/2007)
~~~~~~~~~
This is what your query would look like using the column names you provided as an example:
CREATE TABLE dbo.TEMP_myTable (id, Name, address, phone) ON [PRIMARY]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[myTable]') AND type IN (N'U'))
DROP TABLE [dbo].[myTable]
GO
EXECUTE sp_rename N'dbo.TEMP_myTable', N'myTable','OBJECT'
GO
---------------
Just change the any instances of "myTable" above with the actual name of your table and you should be good to go. Of course, you won't want to forget to add column constraints (int, varchar, primary key, etc.) after each column name too.
you also might want to add a step in there that transfers data from 'myTable' to 'TEMP_myTable' before dropping :w00t:
EDIT - I just noticed that this thread is nearly 5 years old... hope somebody else caught that back when 😛
-
Jeff Moden (7/25/2011)
For all those balking about column order not mattering in a table, I absolutely agree when it comes to programming.But, for troubleshooting, it's sometimes very helpful and can save some real time to have columns display in SSMS (either in the Explorer or from a TOP 100 * look-see) in an "expected" order especially if there are a decent number of tables with many columns.
Reading through the posts, no one has yet suggested implementing views as a solution to the column order dependency issue. Ideally I only let end users call stored procedures, but when they need direct query access, I give them views, so I have more control over the column positions without having to alter the underlying tables.
Also, even from the perspective of database development and administration, I occasionally have to BCP data from QA or Production to Development. I find views helpful for that as well, because the column order is not the same across environments and views are a lot easier to create and work with than those damn BCP format files, at least when loading relatively small datasets of less than 1,000,000 rows.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
-
Uripedes Pants (7/26/2011)
you also might want to add a step in there that transfers data from 'myTable' to 'TEMP_myTable' before dropping :w00t:EDIT - I just noticed that this thread is nearly 5 years old... hope somebody else caught that back when 😛
Yes, of course. I imagined the OP to be discussing the addition of a column prior to any data population. But, just to be safe, I should have added the following between the creation and dropping:
//Do data migration here (if you have any)
Has it really been five years? Jeez...
-
I have a comment here. Column order does matter with bulk insert. For instance:
Bulk Insert db.schema.table
FROM '\\someplace\PrvMLCoreExt.txt'
WITH (
DATAFILETYPE='char',
FIELDTERMINATOR=','
);
If import has an extra comma in it then import dies. Yes do error handling, nevertheless column order does matter if your way of error handling is to insert a NULL table field to capture this error in data at and fix at a later time.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply