代码之家 › 专栏 › 技术社区 › David Clarke

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

ddl tsql sql-server

David Clarke · 技术社区 · 14 年前

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才能更新列。我试过各种排列,但进展不快。

4 回复 | 直到 14 年前

Aaronaught 14 年前

不要我需要它。

你需要放一个 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

Ola Berntsson 7 年前

我自己也经常为这个问题而烦恼,不幸的是,我在书中提出了解决办法 Aaronaught's answer 当涉及@parameters和'string'时,会很快变得混乱。然而,我通过利用同义词的用法找到了一个不同的解决方法:

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

mdma 14 年前

尝试在ALTER表之后添加一个“GO”语句。

这对我来说是个新闻,但上面说 here 一批中的所有语句(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

JakeD 6 年前

尽管接受的答案确实有效,但对于更复杂的情况,可以使用临时表将数据保存到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

srbrills 5 年前

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] 如果那列不存在。如果添加了新列,则将使用默认值填充现有记录,在本例中,默认值为 1