Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Trouble creating new field on table

Posted on 2013-06-11
4
Medium Priority
?
214 Views
Last Modified: 2013-06-14
I'm trying to insert a new field into a table and I'm getting the following error:

Saving changes is not permitted.  The changes you have made require the following tables to be dropped and re-created.  You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.
0
Comment
Question by:hrolsons
  • 3
4 Comments
 
LVL 11

Expert Comment

by:Louis01
ID: 39239530
In SQL Server Management Studio, go to Tools, Options, [Designers, Table and Database designers]. Remove the checkbox against [Prevent saving changes that require table re-creation]

Keep in mind that you can loose all data in the table. In such cases, it is frequently a good idea to rename the table, recreate it and import the data.
0
 

Author Comment

by:hrolsons
ID: 39239548
I would rather do it the safe way.  I can handle renaming.  Recreate, would that be Script-Create To and add my new field into the SQL.  And then I'm not sure how to import.
0
 
LVL 11

Accepted Solution

by:
Louis01 earned 2000 total points
ID: 39239582
You could right click on the table name in Object Explorer and generate a new create script from there to alter.

To import, see INSERT INTO SELECT FROM

If your simply want to add a new column to a table, consider that you are probably specifying it as NOT NULL, thus the recreating of the table. Maybe you can set it as NULLABLE initially, set some value and then make it NOT NULLABLE.

For help on Alter Table Scripts, see ALTER TABLE
0
 
LVL 11

Expert Comment

by:Louis01
ID: 39246714
hrolsons

Did you manage?

Louis
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question