February 11, 2009 at 12:22 am
Hi
I am having a table with 1 lakhs record and having 50 columns. Now i have to change the order of the column i.e 4th and 5th column should be moved to 15th and 16th column of the table. I want to change the structure of the table without loosing the data. Any help?
Latha
February 11, 2009 at 12:29 am
Hi Latha,
Not sure on why you want to do it, as the need may have some other better solution.
Anyways, as the number of recors are very less you can create another table with the structure you want and write a query like below..
insert into newtable(structure you want)
select column names(order them by ur need) from old table.
hope this helps
Regards,
Sriram
Sriram
February 11, 2009 at 12:38 am
Hi
Thank you for your reply. But I am having more than 50 columns. Any other better solution?
Latha
February 11, 2009 at 12:52 am
hmm... should not take muc time. In Query analyser you can generate both Inser and Select scripts, just change the order for the two columns that you need. max 10-20 min work.
1. create new table
2. dump data
3. drop old table
4. rename new table with old table
Sriram
February 11, 2009 at 1:08 am
well you see if you think writing all the 50 columns is stoping you from going with the above mentioned solution....let me share with you a small tip to counter that.....
Go to your object explorer, select the table you wish to use then expand it such that you get to see the columns folder,etc...
Expand the columns folder, and here where the magic begins....
Click of the Columns folder and then drag that on to ur query analyser...
Tadaa!!! there all columns seperated by commas.!!!....Thank me later...
Now a solution for the problem...my approach to your problem would be something like this..
SELECT (list all the columns here in the order you want it)
INTO dbo.Table1
FROM dbo.UrTable
then using the alter table function recreate the constraints... Now if u have a identity column in your table you could modify the query like this
select identity(int,1,1) as ColWhichIsIden, (remaining list of columns)
INTO dbo.Table1
FROM dbo.UrTable
Hope this helps.....:)
February 11, 2009 at 1:11 am
Now if you would like to generate insert scripts for your table please refer this script that I had created for this very purpose.
http://www.sqlservercentral.com/scripts/insert+script+generator/65407/
February 11, 2009 at 2:24 am
Earlier how i was struggled to list out the columns in the tables because all my tables are more number of columns. Your idea of just dragging and dropping is so easy.
Thank You very much Mr.Linson.Daniel for helping me out! 🙂
Latha
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply