I need to create a new table from another table and then add a primary key to the new table. In order to do that, the id column cannot be null. So, I alter the id column to remove the nullability and then add the primary key.
If I run this as a batch, it fails saying "Cannot define PRIMARY KEY constraint on nullable column in table 'newTable'". If I run this line-by-line in the query analyzer, it runs fine. If I add a GO after the first ALTER TABLE statement, it runs fine as a batch.
Unfortunately, I need to run this as stored procedure, so I cannot use GO. What do I need to change to let this run as a batch without submitting the first two statements as a batch, and then running the last statement?
SELECT * INTO newTable FROM oldTable
ALTER TABLE [newTable] ALTER COLUMN id int NOT NULL
ALTER TABLE [newTable] ADD CONSTRAINT [PK_newTable] PRIMARY KEY CLUSTERED
) ON [PRIMARY]