• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 377
  • Last Modified:

adding a column in the middle of the table

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
hp746
Asked:
hp746
  • 3
  • 2
  • 2
  • +2
4 Solutions
 
s_chilkuryCommented:
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
 
hp746Author Commented:
can't i achive using script
0
 
lcohanDatabase AnalystCommented:
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
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
lcohanDatabase AnalystCommented:
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
 
hp746Author Commented:
with out designer how can i do it from script?
0
 
Anthony PerkinsCommented:
>>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
 
Anthony PerkinsCommented:
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
 
derekkrommCommented:
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
 
lcohanDatabase AnalystCommented:
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

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now