Upgrading tables

[Vaderman]

I've got a table that needs altering but this table has existing data, so the premiss is that I back up the data into a tempory table:

select * into #temptable from DataTable

now at this stage I have the data stored. Next I drop a column from DataTable in the usual manner. It is at this point I wish
to move all the data back from #temptable to DataTable minus the dropped column. BUT, using this approach, I would have to declare variables
for each of the columns of data that I wish to migrate over to the amended DataTable. This would also require me to use a loop to get
the rows out from #temptable and migrate them over to DataTable using insert... values. Is this the only approach that I'm left with, or is there a less long-winded way of achieveing this?

Regards

[exterminator]

Hey Vaderman, I got another easier and pretty way of doing this but you have not told us what database you are using and hence I will provide the solution assuming MS SQL Server.

There is a stored proc provided by MS SQL Server - sp_rename (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ra-rz_3ns5.asp) that you can use to rename your current table. So what you do is:

1. Rename your current table.
2. Create a table with altered columns using a CREATE TABLE.
3. Use SELECT INTO with fewer columns to insert data from the old renamed table (not including the columns you want to copy over i.e. the ones dropped)
4. Verify the integrity of data in the new table (which has the same name as the renamed table originally had)
5. Drop the renamed table.

That's it. No loops required. Hope this helps :) . Regards.

[exterminator]

You would also want to disable indexes and triggers etc for better performance of job. Here - Disabling indexes (http://msdn2.microsoft.com/en-us/library/ms177406.aspx) and re-build them once you are done with all the stuff. Regards.