Test Column существует, добавить столбец и обновить столбец

Я пытаюсь написать обновление базы данных SQL Server script. Я хочу проверить наличие столбца в таблице, а затем, если он не существует, добавьте столбец со значением по умолчанию и, наконец, обновите этот столбец на основе текущего значения другого столбца в той же таблице. Я хочу, чтобы этот script выполнялся несколько раз, при первом обновлении таблицы и последующих запусках следует игнорировать script. В настоящее время мой script выглядит следующим образом:

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, прежде чем я смогу обновить столбец. Я пробовал разные перестановки, но я быстро не успеваю.

Ответ 1

Этот script не будет успешно запущен, если столбец уже не существует, и это именно то, что вам не нужно.

Сценарии SQL должны быть проанализированы до того, как они будут выполнены. Если столбец не существует во время анализа script, тогда синтаксический анализ завершится с ошибкой. Не имеет значения, что ваши сценарии впоследствии создают столбец; синтаксический анализатор не знает этого.

Вам нужно добавить оператор GO (разделитель пакетов), если вы хотите получить доступ к только что добавленному столбцу. Однако, как только вы это сделаете, вы больше не сможете поддерживать какой-либо поток управления или переменные из предыдущей партии - это похоже на выполнение двух отдельных скриптов. Это затрудняет одновременное выполнение как DDL, так и DML.

Простейшее обходное решение, которое я, вероятно, рекомендовал бы вам, потому что ваш DML не очень сложный, заключается в использовании динамического SQL, который парсер не будет пытаться анализировать до "runtime":

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

Ответ 2

Меня часто раздражает эта проблема, и, к сожалению, решение, предложенное в ответе Aaronaught, быстро становится беспорядочным, когда задействуются @parameters и "strings". Тем не менее, я нашел другое обходное решение, используя использование синонимов:

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

Ответ 3

Хотя принятый ответ действительно работает, для более сложного случая вы можете использовать временную таблицу для сохранения данных после инструкции 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

Ответ 4

Если вы используете хотя бы 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. Если столбец уже существует, записи не будут изменены.

Ответ 5

Попробуйте добавить инструкцию "GO" после ALTER TABLE.

Это была новость для меня, но она говорит здесь, что все утверждения в пакете (те, которые предшествуют GO) скомпилированы в один запрос plan.) Не имея GO в SQL, весь план фактически является одним запросом.

EDIT: Поскольку 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