Test de l'existence d'une colonne, ajout d'une colonne et mise à jour d'une colonne.

Question

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 enfin mettre à jour cette colonne en fonction de 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 en mettant à jour la table et lors des exécutions suivantes, le script doit être ignoré. Mon script ressemble actuellement à ce qui suit :

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

Le serveur SQL renvoie l'erreur "Invalid column name 'IsDownloadable'", c'est-à-dire que je dois valider la DDL avant de pouvoir mettre à jour la colonne. J'ai essayé plusieurs permutations mais je n'arrive à rien rapidement.

Answer 1

Ce script ne s'exécutera pas avec succès à moins que la colonne n'existe déjà, ce qui est exactement le cas lorsque vous Ne le fais pas. en ont besoin.

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é, alors l'analyse syntaxique échouera. Il importe peu que vos scripts créent la colonne plus tard ; l'analyseur syntaxique n'a aucun moyen de le savoir.

Vous devez mettre en place un GO (séparateur de lots) si vous voulez 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 si vous exécutiez deux scripts séparés. C'est comme si vous exécutiez deux scripts distincts. Il est donc difficile d'effectuer des opérations DDL et DML, de manière conditionnelle, en même temps.

La solution de contournement la plus simple, que je vous recommanderais probablement parce que votre DML n'est pas très complexe, consiste à utiliser du SQL dynamique, que l'analyseur n'essaiera pas d'analyser avant le "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

Answer 2

J'ai moi-même souvent été gêné par ce problème, et malheureusement la solution proposée dans Réponse d'Aaronaught devient rapidement désordonnée lorsque des @paramètres et des "chaînes" sont impliqués. Cependant, j'ai trouvé une solution différente 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

Answer 3

Bien que la réponse acceptée fonctionne, pour un cas plus compliqué, vous pouvez utiliser une table temporaire pour conserver les données après l'instruction GO. Veillez simplement à ne pas oublier de la nettoyer par la suite.

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

Answer 4

Si vous utilisez au moins SQL Server 2008, vous pouvez spécifier WITH VALUES au moment de l'ajout de la colonne, ce qui remplira les enregistrements existants avec la valeur par défaut de cet attribut.

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

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

END

Cela ajoutera une nouvelle colonne, [Is4WheelDrive] à la table [dbo].[Trucks] si cette colonne n'existe pas. La nouvelle colonne, si elle est ajoutée, remplira les enregistrements existants avec la valeur par défaut, qui dans ce cas est une valeur BIT de 1 . Si la colonne existe déjà, aucun enregistrement ne sera modifié.

Answer 5

Essayez d'ajouter une instruction "GO" après le ALTER TABLE.

C'était nouveau pour moi, mais ça dit aquí que toutes les déclarations d'un lot (celles qui précèdent le GO) sont compilées en un seul plan de requête). Sans GO dans le SQL, le plan entier est effectivement une seule requête.

EDIT : Puisque GO donne une erreur de syntaxe (ce qui me semblait étrange), j'ai créé quelque chose de similaire, et j'ai trouvé que ceci fonctionnait

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