Test colonne existe, ajouter colonne, et mettre à jour colonne

j'essaie d'écrire un script de mise à jour de la base de données du serveur SQL. Je veux tester l'existence d'une colonne dans une table, puis si elle n'existe pas ajouter la colonne avec une valeur par défaut, et finalement mettre à jour cette colonne basée sur la valeur actuelle d'une colonne différente dans la même table. Je veux que ce script soit exécutable plusieurs fois, la première fois que la table est mise à jour et lors des éditions suivantes, le script doit être ignoré. Mon script ressemble actuellement le suivant:

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 Le serveur renvoie l'erreur "nom de colonne invalide 'IsDownloadable'", c'est-à-dire que je dois propager le DDL avant de pouvoir mettre à jour la colonne. J'ai essayé différentes permutations, mais je n'arrive à rien.

44
demandé sur David Clarke 2010-05-04 03:43:04

4 réponses

ce script ne fonctionnera pas avec succès à moins que la colonne n'existe déjà, ce qui est exactement quand vous ne pas besoin d'elle.

les Scripts SQL doivent être analysés avant de pouvoir être exécutés. Si la colonne n'existe pas au moment où le script est analysé, puis l'analyse échoue. Peu importe que vos scripts créent la colonne plus tard; l'analyseur n'a aucun moyen de le savoir.

vous devez mettre un GO déclaration (séparateur de lots) si vous voulez pour accéder à une colonne que vous venez d'ajouter. Cependant, une fois que vous avez fait cela, vous ne pouvez plus maintenir le flux de contrôle ou les variables du lot précédent - c'est comme exécuter deux scripts séparés. Cela rend difficile de faire à la fois DDL et DML, conditionnellement, en même temps.

la solution la plus simple, que je vous recommande probablement parce que votre DML n'est pas très complexe, est D'utiliser le SQL dynamique, que L'analyseur n'essaiera pas de traiter avant "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
75
répondu Aaronaught 2010-05-04 01:01:21

j'ai souvent été ennuyé par ce problème moi-même, et malheureusement la solution suggérée dans la réponse D'Aaronaught devient rapidement désordonné quand @parameters et' strings ' sont impliqués. Cependant, j'ai trouvé une solution à ce problème en exploitant l'utilisation de synonymes:

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
1
répondu Ola Berntsson 2018-02-02 20:43:20

bien que la réponse acceptée fonctionne, pour un cas plus compliqué, vous pouvez utiliser une table temporaire pour persister les données au-delà de L'énoncé GO. juste assurez-vous que vous n'oubliez pas de nettoyer après.

Par exemple:

-- 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
0
répondu Jaked222 2018-04-20 13:22:17

essayez d'ajouter une déclaration" GO " après la table D'ALTER.

C'est nouvelles pour moi, mais il est dit ici que toutes les déclarations d'un lot (celles qui précèdent le GO) sont compilées dans un plan de requête.) Sans aucun GO dans le SQL, le plan entier est effectivement une requête.

EDIT: puisque GO donne une erreur de syntaxe (ce qui m'a semblé étrange), j'ai créé quelque chose de similaire, et j'ai trouvé que cela a fonctionné

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
-1
répondu mdma 2010-05-04 00:52:52