SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to insert new column in an old table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Abid
Posting Yak Master

Pakistan
102 Posts

Posted - 01/16/2014 :  07:30:16  Show Profile  Reply with Quote
Hi. I have a table SaleMaster that is :

SaleID, SaleTotalAmount, SalePaidAmount, SaleDisocunt, SaleNetBal, SaleDate


and SaleDetail table.:

SaleID, ProdID, SaleUnitPrice, SaleQty, Saletotal

SaleID, ProdID will be composite key

Now i want to add a new field called: CustomerID (customerInfo is a separate table) as a foreign key so my new table should like this:

SaleID, ProdID, CustomerID, SaleUnitPrice, SaleQty, SaleTotal


SaleID, ProdID and CustomerID will be composite key. But when i insert the new column so it gives an error (Saving Changes is not permitted. The changes you have made). Please guide me that how do i insert it now.

nagino
Yak Posting Veteran

Japan
55 Posts

Posted - 01/16/2014 :  18:26:26  Show Profile  Reply with Quote
If you use SSMS, check followings
http://msdn.microsoft.com/en-us/library/bb895146.aspx

-------------------------------------
From Japan
Sorry, my English ability is limited.

Edited by - nagino on 01/16/2014 18:27:18
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/17/2014 :  01:54:28  Show Profile  Reply with Quote
quote:
Originally posted by Abid

Hi. I have a table SaleMaster that is :

SaleID, SaleTotalAmount, SalePaidAmount, SaleDisocunt, SaleNetBal, SaleDate


and SaleDetail table.:

SaleID, ProdID, SaleUnitPrice, SaleQty, Saletotal

SaleID, ProdID will be composite key

Now i want to add a new field called: CustomerID (customerInfo is a separate table) as a foreign key so my new table should like this:

SaleID, ProdID, CustomerID, SaleUnitPrice, SaleQty, SaleTotal


SaleID, ProdID and CustomerID will be composite key. But when i insert the new column so it gives an error (Saving Changes is not permitted. The changes you have made). Please guide me that how do i insert it now.


see
http://visakhm.blogspot.in/2014/01/issues-in-altering-objects-through-ssms.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Abid
Posting Yak Master

Pakistan
102 Posts

Posted - 01/18/2014 :  09:33:47  Show Profile  Reply with Quote
Hi. I've seen similar sites on google but I read a post in a forum in which it is mentioned that Unchecking this option is not safe and may cause problem in future in DB. What about that.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/19/2014 :  02:21:19  Show Profile  Reply with Quote
quote:
Originally posted by Abid

Hi. I've seen similar sites on google but I read a post in a forum in which it is mentioned that Unchecking this option is not safe and may cause problem in future in DB. What about that.


Nope thats wrong
Unchecking this option has no effect on db as such. It is just an extra restriction put by designer. So far as you've access and are supposed to change the objects you can go ahead and do it.
You will be able to complete the change only if you're given access to modify the objects.


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/20/2014 :  08:41:03  Show Profile  Reply with Quote
quote:
Originally posted by Abid

Hi. I've seen similar sites on google but I read a post in a forum in which it is mentioned that Unchecking this option is not safe and may cause problem in future in DB. What about that.


When you insert a new column amongst others (i.e. not "at the end"), or change a column in certain ways, then the Script that SQL generates does this:

Drop constraints
Create new temporary table
Recreate some of the constrains on the temporary table (e.g. Defaults)
Copy all the data from Old table to Temporary table
Drop old table
Rename Temp table to original name
Recreate remainder of constraints (e.g. Foreign Keys)

If the table has a lot of rows then creating the new temporary table, and copying the data into it, can take a significant amount of time, and disk space, including perhaps extending the data file, and maybe filling up the disk ...

It is for that reason that it is not allowed normally, and you have to explicitly set to option to allow it.

Take a backup of your database first (always good practice ), and make sure noone is actually trying to update data in the table when you make the change.
Go to Top of Page

Abid
Posting Yak Master

Pakistan
102 Posts

Posted - 01/23/2014 :  13:48:58  Show Profile  Reply with Quote
@ Visakh, Bundles of thanks for making my mind clear for this ambiguity. Thank you so much, you really enhanced my knowledge.

@ Kristen, what an adorable explanation you provided. It really made me out of vague ideas, cause in the same website, where i read that this option may harm your DB, was also written there that when it creates Temporarily table then you will have to reset the temporarily table by yourself and a lengthy way was provided. But after reading yours and Visakh reply, i am now very clear about this thing.

Thank you very much for your affectionate participation in my thread. :)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/24/2014 :  01:03:51  Show Profile  Reply with Quote
quote:
Originally posted by Abid

@ Visakh, Bundles of thanks for making my mind clear for this ambiguity. Thank you so much, you really enhanced my knowledge.

@ Kristen, what an adorable explanation you provided. It really made me out of vague ideas, cause in the same website, where i read that this option may harm your DB, was also written there that when it creates Temporarily table then you will have to reset the temporarily table by yourself and a lengthy way was provided. But after reading yours and Visakh reply, i am now very clear about this thing.

Thank you very much for your affectionate participation in my thread. :)


No problem. Glad that I could help you out.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000