I am trying to construct a Query that will allow me to add a new INT field to an existing table, eg.
IF NOT EXISTS(select column_name from information_schema.columns
WHERE table_name = 'tblPgVer' AND column_name='tePubQueueStep')
ALTER TABLE tblPgVer
ADD tePubQueueStep int NOT NULL
ALTER TABLE tePubQueueStep ADD CONSTRAINT
DF_tblPgVer_tePubQueueStep DEFAULT '1' FOR tePubQueueStep
However I am getting the following error;
ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'tePubQueueStep' cannot be added to non-empty table 'tblPgVer' because it does not satisfy these conditions.
Could somebody please provide edits to the query that will allow me to do this. Do I need to first add the filed allowing nulls, then set the value of 1 to all blank vales, then add the constriant?