测试列是否存在、添加列和更新列

Question

我正在尝试编写 SQL Server 数据库更新脚本。我想测试表中是否存在列，如果不存在，则添加具有默认值的列，最后根据同一个表中不同列的当前值更新该列。我希望这个脚本可以运行多次，第一次更新表，并且在后续运行中应该忽略该脚本。我的脚本当前如下所示：

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'PurchaseOrder' AND COLUMN_NAME = 'IsDownloadable')
BEGIN

ALTER TABLE [dbo].[PurchaseOrder] ADD [IsDownloadable] bit NOT NULL DEFAULT 0

UPDATE [dbo].[PurchaseOrder] SET [IsDownloadable] = 1 WHERE [Ref] IS NOT NULL

END

SQL Server 返回错误“无效的列名 'IsDownloadable'”，即我需要先提交 DDL，然后才能更新列。我尝试过各种排列，但一无所获。

Answer 1

除非该列已经存在，而这正是您不需要它的时候，否则该脚本不会成功运行。

SQL 脚本在执行之前必须经过解析。如果解析脚本时该列不存在，则解析将失败。您的脚本稍后创建该列并不重要；解析器无法知道这一点。

如果您想访问刚刚添加的列，则需要输入 GO 语句（批分隔符）。但是，一旦执行此操作，您就无法再维护上一批中的任何控制流或变量 - 就像运行两个单独的脚本一样。这使得同时有条件地执行 DDL 和 DML 变得很棘手。

最简单的解决方法（我可能会推荐给您，因为您的 DML 不是很复杂）是使用动态 SQL，解析器在“运行时”之前不会尝试解析：

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'PurchaseOrder' AND COLUMN_NAME = 'IsDownloadable')
BEGIN

    ALTER TABLE [dbo].[PurchaseOrder] ADD 
        [IsDownloadable] bit NOT NULL DEFAULT 0

    EXEC sp_executesql
        N'UPDATE [dbo].[PurchaseOrder] SET [IsDownloadable] = 1 WHERE [Ref] IS NOT NULL'

END

Answer 2

我自己经常对这个问题感到恼火，不幸的是，当 @parameters 和 '涉及字符串。但是，我通过利用同义词的使用找到了不同的解决方法：

IF(COL_LENGTH('MyTable', 'NewCol') IS NULL)
BEGIN
    ALTER TABLE MyTable ADD NewCol VARCHAR(16) NULL;

    CREATE SYNONYM hack FOR MyTable;
    UPDATE hack SET NewCol = 'Hello ' + OldCol;
    DROP SYNONYM hack;

    ALTER TABLE MyTable ALTER COLUMN NewCol VARCHAR(16) NOT NULL;
END

Answer 3

尝试在 ALTER TABLE 之后添加“GO”语句。

这对我来说是新闻，但它这里说，所有声明一批（GO 之前的批次）被编译成一个查询计划。）如果 SQL 中没有 GO，整个计划实际上就是一个查询。

编辑：由于 GO 给出了语法错误（这对我来说似乎很奇怪），我创建了类似的东西，并发现这有效

declare @doUpdate bit;

SELECT @doUpdate = 0;

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'PurchaseOrder' AND COLUMN_NAME = 'IsDownloadable')
BEGIN
 SELECT @doUpdate=1
END

IF @doUpdate<>0 
   ALTER TABLE [dbo].[PurchaseOrder] ADD [IsDownloadable] bit NOT NULL DEFAULT 0

IF @doUpdate<>0
  UPDATE [dbo].[PurchaseOrder] SET [IsDownloadable] = 1 WHERE [Ref]=0

COMMIT TRAN

Answer 4

尽管接受的答案确实有效，但对于更复杂的情况，您可以使用临时表来保存 GO 语句之后的数据。只要确保记得之后将其清理干净即可。

例如：

-- Create a tempTable if it doesn't exist. Use a unique name here
IF OBJECT_ID('tempdb..#tempTable') IS NOT NULL DROP TABLE #tempTable
CREATE TABLE #tempTable (ColumnsCreated bit)

-- Create your new column if it doesn't exist. Also, insert into the tempTable.
IF NOT EXISTS (
    SELECT * FROM   INFORMATION_SCHEMA.COLUMNS 
    WHERE  TABLE_NAME = 'targetTable' AND COLUMN_NAME = 'newColumn')
BEGIN
    INSERT INTO #tempTable VALUES (1)

    ALTER TABLE .dbo.targetTable ADD newColumn [SMALLINT] NULL ;
END

GO

-- If the tempTable was inserted into, our new columns were created.
IF (EXISTS(SELECT * FROM #tempTable))
    BEGIN
    -- Do some data seeding or whatever
    END

-- Clean up - delete the tempTable.
IF OBJECT_ID('tempdb..#tempTable') IS NOT NULL DROP TABLE #tempTable

Answer 5

如果您至少使用 SQL Server 2008，则可以在添加列时指定 WITH VALUES，这将使用该属性的默认值填充现有记录。

IF COL_LENGTH('[dbo].[Trucks]', 'Is4WheelDrive') IS NULL
BEGIN

    ALTER TABLE [dbo].[Trucks]
    ADD [Is4WheelDrive] BIT NULL DEFAULT 1
    WITH VALUES;

END

如果该列不存在，这会将新列 [Is4WheelDrive] 添加到表 [dbo].[Trucks] 中。新列（如果添加）将使用默认值填充现有记录，在本例中为 BIT 值 1。如果该列已存在，则不会修改任何记录。