我正在使用sql server 2005
其思想是创建一个列,将值更新为0,然后使其不可空,并将现有的PK重新添加到列表中。
到目前为止我得到了这个:
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable' AND COLUMN_NAME = 'Division')
BEGIN
ALTER TABLE MyTable ADD Division int NULL
UPDATE MyTable SET Division = 0
ALTER TABLE MyTable ALTER COLUMN Division int NOT NULL
ALTER TABLE [dbo].[MyTable] DROP CONSTRAINT [PK_MyTable]
ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
[ID] ASC,
[OfferCode] ASC,
[StationCode] ASC,
[Market] ASC,
[Division] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END
问题是我需要一个“GO”语句来创建新列,否则在尝试将值更新为0时会出现错误。如果我提出一个“go”语句,那么if子句不起作用(因为该列现在存在)。我想把这个状态设置为一个变量,比如@ColumnWasCreated,但是“GO”语句就像启动一个全新的脚本,变量的值就丢失了
有什么想法吗??