June 27, 2016 at 3:45 am
Hi,
I need to add new column to the table, for this requirement is move the data to temp table and drop the table and create the same table with additional column and repopulate data along with new columns. I know you could say I can use straight alter statement on table, becuase of some restrictions using Merge statement for Columnset, we need to follow this flow.
Could anyone provide some suggestions for
Many Thanks
June 27, 2016 at 3:52 am
Suggestions for what?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 27, 2016 at 4:01 am
Example Scripts for storing existing table structure and data into temp table, and drop the table and create same table with additional column and repopulate data from temp table.
June 27, 2016 at 4:13 am
Script out the CREATE TABLE from management studio, along with all indexes and constraints. Use SELECT INTO to put the data into a temp table. Drop or rename the table (preferably rename)
Make the changes needed to the CREATE TABLE that you scripted out. Run it.
Use INSERT INTO ... SELECT to put the data back from the temp table.
Make sure you have a good backup when you start, if anything goes wrong you may need to restore.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 27, 2016 at 8:44 am
Sangeeth878787 (6/27/2016)
Hi,I need to add new column to the table, for this requirement is move the data to temp table and drop the table and create the same table with additional column and repopulate data along with new columns. I know you could say I can use straight alter statement on table, becuase of some restrictions using Merge statement for Columnset, we need to follow this flow.
Could anyone provide some suggestions for
Many Thanks
Use the table designer in SSMS to generate the script for you. It can be done without actually executing it.
--Jeff Moden
June 27, 2016 at 9:12 am
To add to what Jeff said, once you use the Table Designer to make changes, don't click "save".
Instead, use Table Designer | Generate Change Script or click the icon that looks like a document with a disk on it.
Save the script and discard the changes from the table designer.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply