T-SQL: Script to alter a table with a new column inserted in the middle of the table?
-
A simple "alter table add column" statement adds my column at the end of the table. Usually this is OK, but this time I need to add it to the middle of the table. The only method I've come up with requires I copy the data to a #tmp table, drop/recreate the original table with the new schema, and then import the original data into it. I suspect this will kill my dependencies, and I *really* don't want to do that. Any ideas? I need to do this in a script, so please don't think you are helping me by sending me to the Enterprise Manager's GUI for table-designing. Thanks!
-
Answer:
I'm curious as to why you feel you need to insert the new column into the middle of the table? The physical ordering of the columns within a row shouldn't make any difference for retrieval purposes unless you have big BLOBlike objects currently there (and these sort of objects are best relegated to their own tables for just such a reason).
Solly Llama NOR★CAL R&S at Yahoo! Answers Visit the source
Other answers
there is no way to put column into middles of columns of existing table. If you are very desperate then you have following option, remember new column should be NULLABLE: suppose your table name is tblA, and it has following columns test1 varchar(50) null, test2 varchar(50) null Now use following script: Begin Transaction Create Table tblB ( test1 varchar(50) null, test3 varchar(50) null, -- NEW COLUMN IN MIDDLE (NULLABLE) test2 varchar(50) null ) go if exists(select * from tblA) exec('insert into tblB (test1, test2) select test1, test2 from tblA with (holdlock tablockx)') go drop table tblA go execute sp_rename 'tblB', 'tblA', 'object' go commit
Related Q & A:
- How to add primary key from multiple table as a foreign key in a table in sql server 2008?Best solution by stackoverflow.com
- How to add a new table to a data source?Best solution by technet.microsoft.com
- How to change column data's as a separate column wise format in a SQL Server?Best solution by stackoverflow.com
- Why, do you think, do fishers converge at a fishery when a new fish becomes popular?Best solution by Yahoo! Answers
- What is the best way to negotiate a salary for a new position?Best solution by Yahoo! Answers
Just Added Q & A:
- How many active mobile subscribers are there in China?Best solution by Quora
- How to find the right vacation?Best solution by bookit.com
- How To Make Your Own Primer?Best solution by thekrazycouponlady.com
- How do you get the domain & range?Best solution by ChaCha
- How do you open pop up blockers?Best solution by Yahoo! Answers
For every problem there is a solution! Proved by Solucija.
-
Got an issue and looking for advice?
-
Ask Solucija to search every corner of the Web for help.
-
Get workable solutions and helpful tips in a moment.
Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.