Drop the table and add the column

Question

Drop the table and add the column

Sangeeth Raj

Hall of Fame

Points: 3541
More actions
June 27, 2016 at 3:45 am

#317378

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sangeeth Raj Hall of Fame Points: 3541 More actions · Answer 2

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 4

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 734552 More actions · Answer 5

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.