|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Add a Column at Particullar Position n MS SQL ServerHello All,
I want to add a new column at a particular position in table without droping/recreating table. How to do it? Thanks & Regards Ravi Kandukuri | Database Administrator | SUVI Information Systems Pvt Ltd (CMMI 3) | 582 MG Road Indore - 452 003 MP [India] | Work +91-731-243-6611-13 Ext 442 | Mobile +91-98934-42338 | Fax +91-731-2436615 | www.suviinfo.com Hi Ravi
If you go into the design view of your table, you can right click on the position where you want the new table, and select insert column Hope this helps. Leon Ravi Kandukuri wrote: Show quoteHide quote > Hello All, > I want to add a new column at a particular position in table without > droping/recreating table. How to do it? > > Thanks & Regards > Ravi Kandukuri | Database Administrator | SUVI Information Systems Pvt Ltd > (CMMI 3) | 582 MG Road Indore - 452 003 MP [India] | Work > +91-731-243-6611-13 Ext 442 | Mobile +91-98934-42338 | Fax +91-731-2436615 | > www.suviinfo.com Just be aware that the GUI tools will create a new table, copy all rows, drop old table and rename
new table. -- Show quoteHide quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Leon" <leon.vandenb***@hambisana.co.za> wrote in message news:1157352262.620748.273420@e3g2000cwe.googlegroups.com... > Hi Ravi > > If you go into the design view of your table, you can right click on > the position where you want the new table, and select insert column > > Hope this helps. > Leon > > Ravi Kandukuri wrote: >> Hello All, >> I want to add a new column at a particular position in table without >> droping/recreating table. How to do it? >> >> Thanks & Regards >> Ravi Kandukuri | Database Administrator | SUVI Information Systems Pvt Ltd >> (CMMI 3) | 582 MG Road Indore - 452 003 MP [India] | Work >> +91-731-243-6611-13 Ext 442 | Mobile +91-98934-42338 | Fax +91-731-2436615 | >> www.suviinfo.com > Hello All,
Thanks alot for prompt responce. My requirement is from only T-SQL. Please let me know any possible way. Regards Ravi Kandukuri | Database Administrator | SUVI Information Systems Pvt Ltd (CMMI 3) | 582 MG Road Indore - 452 003 MP [India] | Work +91-731-243-6611-13 Ext 442 | Mobile +91-98934-42338 | Fax +91-731-2436615 | www.suviinfo.com "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in drop old table and renamemessage news:eYJFH6%23zGHA.1288@TK2MSFTNGP03.phx.gbl... > Just be aware that the GUI tools will create a new table, copy all rows, Show quoteHide quote > new table. +91-731-2436615 |> > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > > > "Leon" <leon.vandenb***@hambisana.co.za> wrote in message > news:1157352262.620748.273420@e3g2000cwe.googlegroups.com... > > Hi Ravi > > > > If you go into the design view of your table, you can right click on > > the position where you want the new table, and select insert column > > > > Hope this helps. > > Leon > > > > Ravi Kandukuri wrote: > >> Hello All, > >> I want to add a new column at a particular position in table without > >> droping/recreating table. How to do it? > >> > >> Thanks & Regards > >> Ravi Kandukuri | Database Administrator | SUVI Information Systems Pvt Ltd > >> (CMMI 3) | 582 MG Road Indore - 452 003 MP [India] | Work > >> +91-731-243-6611-13 Ext 442 | Mobile +91-98934-42338 | Fax Show quoteHide quote Hi,
No. There is no TSQL to include a field inbetween fields. Steps to do is:- 1. Pull the data out from table (DTS or BCP OUT) 2. Script the table and all dependants with permissions 3. Drop the table 4. Modify the script to include new column 5. Execute the script 6. Load the data; use DTS or BCP IN This activity can be done with downtime. Ensure that all dependants were created. Thanks Hari SQL Server MVP Show quoteHide quote "Ravi Kandukuri" <ravisrikris***@suviinfo.com> wrote in message news:%23iqYcG$zGHA.1300@TK2MSFTNGP05.phx.gbl... > Hello All, > Thanks alot for prompt responce. My requirement is from only T-SQL. Please > let me know any possible way. > > Regards > Ravi Kandukuri | Database Administrator | SUVI Information Systems Pvt Ltd > (CMMI 3) | 582 MG Road Indore - 452 003 MP [India] | Work > +91-731-243-6611-13 Ext 442 | Mobile +91-98934-42338 | Fax +91-731-2436615 > | > www.suviinfo.com > > "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote > in > message news:eYJFH6%23zGHA.1288@TK2MSFTNGP03.phx.gbl... >> Just be aware that the GUI tools will create a new table, copy all rows, > drop old table and rename >> new table. >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://www.solidqualitylearning.com/ >> >> >> "Leon" <leon.vandenb***@hambisana.co.za> wrote in message >> news:1157352262.620748.273420@e3g2000cwe.googlegroups.com... >> > Hi Ravi >> > >> > If you go into the design view of your table, you can right click on >> > the position where you want the new table, and select insert column >> > >> > Hope this helps. >> > Leon >> > >> > Ravi Kandukuri wrote: >> >> Hello All, >> >> I want to add a new column at a particular position in table without >> >> droping/recreating table. How to do it? >> >> >> >> Thanks & Regards >> >> Ravi Kandukuri | Database Administrator | SUVI Information Systems Pvt > Ltd >> >> (CMMI 3) | 582 MG Road Indore - 452 003 MP [India] | Work >> >> +91-731-243-6611-13 Ext 442 | Mobile +91-98934-42338 | Fax > +91-731-2436615 | >> >> www.suviinfo.com >> > >> >> > > The short answer is "You Don't".
It doesn't matter where the column exists in the table schema. All queries should request fields in the order desired. The long answer is that if you use Enterprise Manager (SQL 2000), and you insert a column (using table design view), behind the scenes, Enterprise Manager will create a new table transfer all the data to the new table, drop the old table, and rename the new table to the same name as the old one. And it can even be more crazy than that. -- Show quoteHide quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "Ravi Kandukuri" <ravisrikris***@suviinfo.com> wrote in message news:O6IU6w%23zGHA.1300@TK2MSFTNGP05.phx.gbl... > Hello All, > I want to add a new column at a particular position in table without > droping/recreating table. How to do it? > > Thanks & Regards > Ravi Kandukuri | Database Administrator | SUVI Information Systems Pvt Ltd > (CMMI 3) | 582 MG Road Indore - 452 003 MP [India] | Work > +91-731-243-6611-13 Ext 442 | Mobile +91-98934-42338 | Fax +91-731-2436615 > | > www.suviinfo.com > > Arnie Rowland (ar***@1568.com) writes:
> The short answer is "You Don't". The only answer is that you can't. If you need to put a column in any otherplace last in the table, you will have to drop and recreate the table. > It doesn't matter where the column exists in the table schema. All queries Of course it matters. From a purely technical aspect it maybe doesn't, but> should request fields in the order desired. for human consumption it certainly does. > The long answer is that if you use Enterprise Manager (SQL 2000), and The idea as such is only crazy, because SQL Server does not have the proper> you insert a column (using table design view), behind the scenes, > Enterprise Manager will create a new table transfer all the data to the > new table, drop the old table, and rename the new table to the same name > as the old one. And it can even be more crazy than that. syntax. However, don't use Enterprise Manager out of the box, because the script it generates have several flaws that needs to be repair before it can be used. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  
Other interesting topics
import 1 mln records = problem
Scheduled VS Made Trigger When Row Changes Processing rows in a trigger Query question Problem updating large number of records concatenate column from rows Doing JOIN with multiple table on multiple fields in each table How to list all indexs from a table? newbie needs help with trouble shooting |
|||||||||||||||||||||||
