I have tried:
ALTER TABLE MY_TABLE
ADD STAGE INT NOT NULL;
But it gives this error message:
ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified
insert a NOT NULL column to an existing table
Viewed 297044
8 Answers
A faster sulotion
If you, like me, needs to do this on a table with a large amount of data the ADD-UPDATE-ALTER option is very slow (can take hours for millions of rows).
If you also don't want a default value on your table, here's the full code for creating a column and dropping the default constraint (pretty much instant even for large tables):
ALTER TABLE my_table ADD column_name INT NOT NULL CONSTRAINT my_table_default_constraint DEFAULT 0
GO
ALTER TABLE my_table DROP CONSTRAINT my_table_default_constraint
GO
This is for SQL Server
Related