[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


adding a column in the middle of the table

Posted on 2011-02-24
Medium Priority
Last Modified: 2012-08-13
How can i add a column  in the middle  of the table through script?

example:i have table  testable,it has columns col1,col2
when i add col3 it shoud go in the middle like col1,col3,col2
Question by:hp746
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2

Expert Comment

ID: 34972928
This can ONLY be done by re-creating the table again.


Create View as an outcome in the preferred selection order say col1, col3, col2

This will solve your purpose.

Author Comment

ID: 34973563
can't i achive using script
LVL 40

Expert Comment

ID: 34974724
IF and ONLY IF the table is reasonable small AND not accessed live online then you could use SQL Management Studio, Right click table, select Design, then on the column list click one column you want to insert before then right click that column you will see a menu pop-up where you can select "Insert Column" before the one selected however...if you generate the SQL Script you will see that the table is actually re-created.

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

LVL 40

Expert Comment

ID: 34974742
And obviously... on the window above after you made all your changes but BEFORE click OK you have the option to "Generate Change Script" that you can save and execute later.

Author Comment

ID: 34975277
with out designer how can i do it from script?
LVL 75

Accepted Solution

Anthony Perkins earned 1000 total points
ID: 34976617
>>with out designer how can i do it from script? <<
You have to:
1. Create the new table with the correct structure.
2. Copy the data from the old table.
3. Drop the old table.
4. Rename the new table to the old name.

This of course assumes there are not foreign constraints, otherwise you would have to drop those first and then re-create them.
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 1000 total points
ID: 34976636
So to answer your specific question asking for a script it would look like this:
-- 1.
CREATE TABLE dbo.Tmp_testable (
	col1 int NULL,			-- Change data types as neceesary
	col3 int NULL,
	col2 int NULL)
-- 2.
INSERT dbo.Tmp_testable (col1, col2)
SELECT col1, col2 FROM dbo.testable 
-- 3.
DROP TABLE dbo.testable
-- 4.
EXEC sp_rename N'dbo.Tmp_testable', N'testable', 'OBJECT'

Open in new window

LVL 15

Assisted Solution

derekkromm earned 500 total points
ID: 34976946
You could technically update the system tables that store the column metadata, but it is highly recommended that you don't go that route. The ordering of the columns shouldn't matter.

LVL 40

Assisted Solution

lcohan earned 500 total points
ID: 34981250
Since Microsoft introduced DM views in SQL, system tables are hidden and this is not even posible not just NOT RECOMMENDED however with the UI design mode you do all you want then easily  generate the script as I described above.

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question