Add new columns

Question

I have a table which has a few columns etc and a few rows.

How can I in SQL Server management add new columns which are also not defaulted to null? 

Answer 1

Hello,

If the table is empty, then you can simply add a not nullable column without any problems.

If the table already contains data, then you eighter have to add a nullable column, update the new column with data and alter the column to not nullable.
Or you have to define a default value for the new not nullable column:

CREATE TABLE #test (ID int NOT NULL);
GO
INSERT INTO #test VALUES (1);

ALTER TABLE #test
ADD myText nvarchar(30) NOT NULL DEFAULT('N/A'); 
GO

SELECT *
FROM #test

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 2

So in management studio, add a nullable column then change all the value to have a value then make it not nullable?

Answer 3

Yes, excatly that way.

If the table contains data you can't add a not null column without a default value defined. So you have to modify your table in three steps.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 4

It says, saving changes is not permitted and I need to drop and recreate.

What am I doing wrong?

Answer 5

that is a known problem with SSMS as it usually does something like drop table and rebuild instead of simply altering the table definition and therefore it does warn you as you would otherwise loose the data in the table.

You need to create the ALTER TABLE statement(s) yourself and execute it in a query windows on the correct database. than you can add columns to an existing table.

---

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 6

So in management studio, add a nullable column then change all the value to have a value then make it not nullable?

would not work in SSMS if the table contains data - see my previous posting

---

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 7

hi,

you can modify SSMS behaviour that prevent changes in the designer that require a recreate underlying command to be executed... in the Tools menu access the Options dialog... in the Designer reference change the "Prevent saving changes that require table re-creation" property to "false" and you can proceed on...

regards

---

http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools http://www.hotelsole.com/