Adding & Modifying Column in MSQL Server 2008

In this post we will see some scenarios while updating or adding columns to your table in microsoft SQL Server 2008. At times it will also help you in the Microsoft SQL Server 2005 and some previous versions.

Scenario 1:

I have set all the relationship perfectly, and some changes need to be made to the table with out loosing any previous relationships.

Step 1 : First of all take the backup of your table data by executing following query

SELECT * INTO Temp FROM Table1

INSERTING DATA TO TEMP

Step 2 : Now we have our data backed up now you can script the table by selecting the option of “Drop and Create To” from left panel.
“Do not run the query”. Just keep it open the query window.

CREATE AND DROP

You can see that your data has been backed up or not by executing

SELECT * FROM Temp

Step 3 : In case if you have any relationship, then delete those by going to design mode and Right click on any of the field and select Relationships. Delete all relationship one by one.

RELATIONSHIP 1

RELATIONSHIP 2

Save the changes and go to previous “Drop and Create To” query window and select only the create part as shown in figure and execute selected query by pressing execute button as highlighted in red box.

Also note that in case you want to change to any column definition or you want to add new column by itself then do it before executing query. So that you will make the changes and will not loose any relationships.

DROP AND RECREATE TABLE

Then scroll down and select only ALTER TABLE part (remaining part except create table part of the query) and execute the query.

DROP AND RECREATE TABLE RELATIONSHIP

Step 4 : Now final step is to take the backed up data to original table.
You can do this by executing following query.

INSERT INTO Table1
SELECT * FROM Temp

Note : In case of columns are different from previous version of Table1 then you have to specify those column names like this

INSERT INTO Table1 (Column1, Column2)
SELECT Column1, Column2 FROM Temp

I would like to also thanks Husain Kachwala, who helped me in some these steps to be successful.

Posted in Database, SQL Server Tagged with:
  • winsome answers i like it

  • This is such a great resource that you are providing and you give it away for free. I enjoy seeing websites that understand the value of providing a prime resource for free. I truly loved reading your post. Thanks!

  • Top site, I had not come across http://www.dhirajranka.com earlier in my searches!
    Carry on the great work!

  • Hello

    This post was interesting, how long did it take you to write?

  • I mistyped this website and luckily I found it again. presently am at my university I added this to favorites so that I can re-read it later regards