Solved

adding a column in the middle of the table

Posted on 2011-02-24
9
367 Views
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
0
Comment
Question by:hp746
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 9

Expert Comment

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

OR

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

This will solve your purpose.
0
 

Author Comment

by:hp746
ID: 34973563
can't i achive using script
0
 
LVL 39

Expert Comment

by:lcohan
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.

0
 
LVL 39

Expert Comment

by:lcohan
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.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

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

Accepted Solution

by:
Anthony Perkins earned 250 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.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 250 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)
GO
-- 2.
INSERT dbo.Tmp_testable (col1, col2)
SELECT col1, col2 FROM dbo.testable 
GO
-- 3.
DROP TABLE dbo.testable
GO
-- 4.
EXEC sp_rename N'dbo.Tmp_testable', N'testable', 'OBJECT'

Open in new window

0
 
LVL 15

Assisted Solution

by:derekkromm
derekkromm earned 125 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.

0
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 125 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.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now