Solved

Trouble creating new field on table

Posted on 2013-06-11
4
202 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 500 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

821 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