Sql Server Problem.. [Resolved]

Posted by Akiii under Sql Server on 8/20/2011 | Points: 10 | Views : 1127 | Status : [Member] | Replies : 6
Hi,
I have a small problem in SQL SERVER 2008 . I have some tables in a database. Those tables contains data. I want to add one column in the table but it is only permitting to enter the row at the end. In the middle, i cannot enter it. Why is this thing happening ?

Please help me if anyone knows the answer ?

Thanks and Regards
Akiii




Responses

Posted by: PandianS on: 8/21/2011 [Member] [MVP] Silver | Points: 50

Up
0
Down

Resolved
Hi

We can not Add any column middle of the existing tab;le structure... without Re-Create the table (Drop and Create)

But, we have some alternate steps here...

If the table already have Id, Name, Place on tbl_cosmetics. OK

If you want to add a new column called Phone next to Name column... like
Id, Name, Phone, Place

So...
/*Copy and Create a new table along with your new column structure(Inclusing column length, Data type)*/

Select Id, Name, Cast('' as Varchar(15)) As Phone, Place Into tbl_cosmetics_Temp from tbl_cosmetics
Go
/*Change your actual table name to something else*/

EXEC Sp_Rename 'tbl_cosmetics','tbl_cosmetics_Old','Object'
Go
/*Change the new table to your actual name*/

EXEC Sp_Rename 'tbl_cosmetics_Temp','tbl_cosmetics','Object'
Go


Note:
1. Before you start this acivity, take a note on constraints / Indexes
2. You have to apply all constraints and Indexes again once you done the above steps...


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Akiii, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: PandianS on: 8/21/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi

1. You already have some columns on Table and also have some Records.
2. You add any new column on the Table.

Are you trying to INSERT a record on that Table ?
or
Are you trying to UPDATE an existing record on that Table ?

If it's INSERT then, It will be at END. If you want to Update any data then, It would be at the actual position of that Record.
This is the actual functionality...

Let me try to understand your actual requirement now!

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Akiii, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Akiii on: 8/21/2011 [Member] Bronze | Points: 25

Up
0
Down
Hi,
Thanks for the reply....

Okay the scenario is:-

I have a database named Report_DB . It contains many tables which contains records respectively.
Suppose, i want to edit the design of a particular table say, tbl_cosmetics . I want to insert a new column/field in the table after some columns, not at the end. But it only allows me to add a new column at the end not at anywhere else.

Please let me know if you have any questions....
I hope i have made the scenario clear..

Regards
Akiii

Akiii, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Akiii on: 8/21/2011 [Member] Bronze | Points: 25

Up
0
Down
Hi Sir,

Thanks for your answer......But why is this that we cannot add column in the middle ?

I mark yours as an answer...
Regards
Akiii

Akiii, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: PandianS on: 8/21/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi

The reason is..

The specific objects "Exclusively locked" by the system and performed by the sequence(How the system prepares the existing schema).

But, If you try to do thiis through the wizard(not writing script).... It'll also not allowed. It throw an Err.... something to be Re-Created....

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Akiii, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Akiii on: 8/21/2011 [Member] Bronze | Points: 25

Up
0
Down
But sir in sql server 2005, same thing working fine....?
Is there more security in sql server 2008 !

Thanks and Regards
Akiii

Akiii, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response