sql 2008 problem to modify table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

sql 2008 problem to modify table

Author

Topic

eugz
Posting Yak Master

210 Posts

Posted - 2009-11-20 : 23:53:03

Hi All.
In sql2005 in design option I modify table structure without any proble. In sql2008 when I try to change indentity from NO to YES (1,1) I got error message:"Saving chandes is not permited. The changes you have made require the following table to be dropped and re-created."
How modify table structure in sql 2008?
Thanks.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-21 : 10:22:55

create an empty table with the same structure but making the field u want identity.

then insert the data from old table to new. drop old table (after you're sure everything is ok) rename new one to name of old one.

you'll need to set identity_insert on for target table.

you should do this all in T-SQL not with the GUI.

something like this:


Create Table newTable (
	id int identity(1, 1) not null,
	name varchar(32),
	address varchar(50)
);
GO

SET IDENTITY_INSERT newTable ON
INSERT	newTable (id, name, address)
SELECT	* FROM oldTable;
SET IDENTITY_INSERT newTable OFF;
GO

-- if all ok
DROP TABLE oldTable;
GO
EXEC sp_rename 'newTable', 'oldTable';
GO

eugz
Posting Yak Master

210 Posts

Posted - 2009-11-21 : 11:12:49

Thanks for replay.
The problem solved very simple. In the Tools - Options menu, select the Designers node, and under Table options, uncheck "Prevent saving changes that require the table to be re-created".

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-21 : 12:39:48

should still learn to do it in SQL and not using the GUI

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources