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.
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
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.
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.
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.
Then scroll down and select only ALTER TABLE part (remaining part except create table part of the query) and execute the query.
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.