Ajouter une colonne avec une valeur par défaut à une table existante dans SQL Server

comment ajouter une colonne avec une valeur par défaut à une table existante dans SQL Server 2000 / SQL Server 2005 ?

2345
demandé sur Ahmad.Tr 2008-09-18 16:30:04
la source

30 ответов

syntaxe:

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES

exemple:

ALTER TABLE SomeTable
        ADD SomeCol Bit NULL --Or NOT NULL.
 CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
    DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.

Notes:

Contrainte Facultative Nom:

Si vous oubliez CONSTRAINT D_SomeTable_SomeCol , alors SQL Server va autogénérer

    une contre-valeur par défaut avec un drôle de nom comme: DF__SomeTa__SomeC__4FB7FEF6

Mention Facultative Avec Valeurs:

Le WITH VALUES n'est nécessaire que lorsque votre colonne est null
1519230920"     et vous voulez la valeur par défaut utilisée pour les enregistrements existants.

Si votre colonne est NOT NULL , elle utilisera automatiquement la valeur par défaut

    pour tous les enregistrements existants, que vous spécifiez WITH VALUES ou non.

comment les Inserts fonctionnent avec une contrainte par défaut:

Si vous insérez un enregistrement dans SomeTable et faites pas spécifier SomeCol valeur de l', Alors il sera par défaut à 0 .

Si vous insérez un enregistrement et spécifier SomeCol valeur de l 'comme NULL (et votre colonne permet nulls),

    alors la contrainte par défaut sera ne sera pas utilisé et NULL sera inséré comme valeur.

Notes ont été basées sur la grande rétroaction de chacun ci-dessous.

Remerciements spéciaux à:

    @Yatrix, @WalterStabosz, @YahooSerious, et @StackMan pour leurs commentaires.

2974
répondu James Boother 2018-07-03 23:44:15
la source
ALTER TABLE Protocols
ADD ProtocolTypeID int NOT NULL DEFAULT(1)
GO

l'inclusion des lignes DEFAULT remplit la colonne existing avec la valeur par défaut, de sorte que la contrainte non nulle n'est pas violée.

875
répondu dbugger 2016-04-19 16:35:13
la source

lors de l'ajout d'une colonne annulable , WITH VALUES assurera que la valeur par défaut spécifique est appliquée aux lignes existantes:

ALTER TABLE table
ADD column BIT     -- Demonstration with NULL-able column added
CONSTRAINT Constraint_name DEFAULT 0 WITH VALUES
200
répondu phunk_munkie 2017-05-26 22:11:03
la source
ALTER TABLE <table name> 
ADD <new column name> <data type> NOT NULL
GO
ALTER TABLE <table name> 
ADD CONSTRAINT <constraint name> DEFAULT <default value> FOR <new column name>
GO
116
répondu ddc0660 2009-07-31 07:46:24
la source
ALTER TABLE MYTABLE ADD MYNEWCOLUMN VARCHAR(200) DEFAULT 'SNUGGLES'
106
répondu Evan V 2012-11-17 03:33:24
la source

attention lorsque la colonne que vous ajoutez a une contrainte NOT NULL , mais n'a pas de contrainte DEFAULT (valeur). La déclaration ALTER TABLE échouera dans ce cas si la table contient des lignes. La solution consiste soit à supprimer la contrainte NOT NULL de la nouvelle colonne, soit à lui fournir une contrainte DEFAULT .

86
répondu jalbert 2008-09-24 20:03:25
la source

la version la plus basique avec deux lignes seulement

ALTER TABLE MyTable
ADD MyNewColumn INT NOT NULL DEFAULT 0
81
répondu adeel41 2012-05-25 18:50:21
la source

utiliser:

-- Add a column with a default DateTime  
-- to capture when each record is added.

ALTER TABLE myTableName  
ADD RecordAddedDate smalldatetime NULL DEFAULT(GetDate())  
GO 
66
répondu JerryOL 2017-05-26 22:10:35
la source

si vous voulez ajouter plusieurs colonnes vous pouvez le faire de cette façon par exemple:

ALTER TABLE YourTable
    ADD Column1 INT NOT NULL DEFAULT 0,
        Column2 INT NOT NULL DEFAULT 1,
        Column3 VARCHAR(50) DEFAULT 'Hello'
GO
58
répondu Gabriel L. 2017-11-14 23:54:08
la source

utiliser:

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}

référence: ALTER TABLE (Transact-SQL) (MSDN)

46
répondu giá vàng 2017-05-26 22:18:28
la source

vous pouvez faire la chose avec T-SQL de la manière suivante.

 ALTER TABLE {TABLENAME}
 ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
 CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}

ainsi que vous pouvez utiliser SQL Server Management Studio aussi en cliquant avec le bouton droit de la souris sur la table dans le menu de conception, définissant la valeur par défaut à la table.

et de plus, si vous voulez ajouter la même colonne (si elle n'existe pas) à toutes les tables dans la base de données, puis utiliser:

 USE AdventureWorks;
 EXEC sp_msforeachtable
'PRINT ''ALTER TABLE ? ADD Date_Created DATETIME DEFAULT GETDATE();''' ;
43
répondu gngolakia 2017-05-26 22:15:40
la source

dans SQL Server 2008-R2, je vais au mode de conception - dans une base de données de test - et ajouter mes deux colonnes en utilisant le concepteur et fait les paramètres avec L'interface graphique, et puis l'infâme clic droit donne l'option générer Script de changement ""!

Bang up fait apparaître une petite fenêtre avec, vous l'avez deviné, le script de changement correctement formaté et garanti au travail. Frapper le bouton facile.

42
répondu Jack 2017-05-26 22:17:22
la source

alternativement, vous pouvez ajouter un défaut sans avoir à nommer explicitement la contrainte:

ALTER TABLE [schema].[tablename] ADD  DEFAULT ((0)) FOR [columnname]

si vous avez un problème avec les contraintes par défaut existantes lors de la création de cette contrainte, alors elles peuvent être supprimées par:

alter table [schema].[tablename] drop constraint [constraintname]
41
répondu Christo 2017-05-26 22:19:09
la source

pour ajouter une colonne à une table de base de données existante avec une valeur par défaut, nous pouvons utiliser:

ALTER TABLE [dbo.table_name]
    ADD [Column_Name] BIT NOT NULL
Default ( 0 )

Voici une autre façon d'ajouter une colonne à une table de base de données avec une valeur par défaut.

un script SQL beaucoup plus complet pour ajouter une colonne avec une valeur par défaut est ci-dessous, y compris vérifier si la colonne existe avant de l'ajouter vérifie également la contrainte et la laisser tomber s'il y en a une. Ce script nomme aussi la contrainte afin que nous puissions avoir une convention de nommage agréable (J'aime DF_) et si non SQL nous donnera une contrainte avec un nom qui a un nombre généré au hasard; il est donc agréable d'être en mesure de nommer la contrainte aussi.

-------------------------------------------------------------------------
-- Drop COLUMN
-- Name of Column: Column_EmployeeName
-- Name of Table: table_Emplyee
--------------------------------------------------------------------------
IF EXISTS (
            SELECT 1
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME = 'table_Emplyee'
              AND COLUMN_NAME = 'Column_EmployeeName'
           )
    BEGIN

        IF EXISTS ( SELECT 1
                    FROM sys.default_constraints
                    WHERE object_id = OBJECT_ID('[dbo].[DF_table_Emplyee_Column_EmployeeName]')
                      AND parent_object_id = OBJECT_ID('[dbo].[table_Emplyee]')
                  )
            BEGIN
                ------  DROP Contraint

                ALTER TABLE [dbo].[table_Emplyee] DROP CONSTRAINT [DF_table_Emplyee_Column_EmployeeName]
            PRINT '[DF_table_Emplyee_Column_EmployeeName] was dropped'
            END
     --    -----   DROP Column   -----------------------------------------------------------------
        ALTER TABLE [dbo].table_Emplyee
            DROP COLUMN Column_EmployeeName
        PRINT 'Column Column_EmployeeName in images table was dropped'
    END

--------------------------------------------------------------------------
-- ADD  COLUMN Column_EmployeeName IN table_Emplyee table
--------------------------------------------------------------------------
IF NOT EXISTS (
                SELECT 1
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_NAME = 'table_Emplyee'
                  AND COLUMN_NAME = 'Column_EmployeeName'
               )
    BEGIN
    ----- ADD Column & Contraint
        ALTER TABLE dbo.table_Emplyee
            ADD Column_EmployeeName BIT   NOT NULL
            CONSTRAINT [DF_table_Emplyee_Column_EmployeeName]  DEFAULT (0)
        PRINT 'Column [DF_table_Emplyee_Column_EmployeeName] in table_Emplyee table was Added'
        PRINT 'Contraint [DF_table_Emplyee_Column_EmployeeName] was Added'
     END

GO

ce sont deux façons d'ajouter une colonne à une table de base de données avec une valeur par défaut.

39
répondu Catto 2017-11-29 22:13:11
la source
ALTER TABLE ADD ColumnName {Column_Type} Constraint

l'article MSDN ALTER TABLE (Transact-SQL) possède toute la syntaxe alter table.

35
répondu Benjamin Autin 2013-07-30 01:11:17
la source

exemple:

ALTER TABLE [Employees] ADD Seniority int not null default 0 GO
27
répondu andy 2014-07-12 10:39:13
la source

cela peut être fait dans le GUI SSMS aussi bien. Je montre une date par défaut ci-dessous, mais la valeur par défaut peut être n'importe quoi, bien sûr.

  1. Placez votre table en mode Création (clic droit sur la table en objet explorer - > Design)
  2. Ajouter une colonne à la table (ou cliquez sur la colonne que vous souhaitez mettre à jour si il existe déjà)
  3. dans la colonne Propriétés ci-dessous, inscrivez (getdate()) ou abc ou 0 ou autre valeur que vous voulez dans le champ valeur par défaut ou liaison comme sur la photo ci-dessous:

enter image description here

25
répondu Tony L. 2018-07-16 18:36:51
la source

exemple:

ALTER TABLE tes 
ADD ssd  NUMBER   DEFAULT '0';
21
répondu Mohit Tamrakar 2015-04-15 08:13:58
la source

SQL Server + Alter Table + Add Column + Default Value uniqueidentifier

ALTER TABLE Product 
ADD ReferenceID uniqueidentifier not null 
default (cast(cast(0 as binary) as uniqueidentifier))
16
répondu Naveen Desosha 2015-07-07 11:38:01
la source

créez D'abord une table avec le nom d'étudiant:

CREATE TABLE STUDENT (STUDENT_ID INT NOT NULL)

ajouter une colonne:

ALTER TABLE STUDENT 
ADD STUDENT_NAME INT NOT NULL DEFAULT(0)

SELECT * 
FROM STUDENT

La table est créée et d'une colonne est ajoutée à une table existante avec une valeur par défaut.

Image 1

16
répondu Laxmi 2017-05-29 11:28:06
la source

Essayez cette

ALTER TABLE Product
ADD ProductID INT NOT NULL DEFAULT(1)
GO
15
répondu Jakir Hossain 2014-09-01 11:17:52
la source
IF NOT EXISTS (
    SELECT * FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME ='TABLENAME' AND COLUMN_NAME = 'COLUMNNAME'
)
BEGIN
    ALTER TABLE TABLENAME ADD COLUMNNAME Nvarchar(MAX) Not Null default
END
14
répondu Jeevan Gharti 2016-06-21 14:29:50
la source

cela a beaucoup de réponses, mais je sens le besoin d'ajouter cette méthode étendue. Cela semble beaucoup plus long, mais il est extrêmement utile si vous ajoutez un champ non NULL à une table avec des millions de lignes dans une base de données active.

ALTER TABLE {schemaName}.{tableName}
    ADD {columnName} {datatype} NULL
    CONSTRAINT {constraintName} DEFAULT {DefaultValue}

UPDATE {schemaName}.{tableName}
    SET {columnName} = {DefaultValue}
    WHERE {columName} IS NULL

ALTER TABLE {schemaName}.{tableName}
    ALTER COLUMN {columnName} {datatype} NOT NULL

ce que cela va faire est d'ajouter la colonne comme un champ null et avec la valeur par défaut, mettre à jour tous les champs à la valeur par défaut (ou vous pouvez assigner des valeurs plus significatives), et enfin il va changer la colonne pour ne pas être NULL.

la raison en est que si vous mettez à jour une table à grande échelle et ajoutez un nouveau champ non nul, il doit écrire à chaque ligne et verrouillera la table entière car il ajoute la colonne et écrit ensuite toutes les valeurs.

cette méthode va ajouter la colonne nullable qui fonctionne beaucoup plus rapidement par elle-même, puis remplit les données avant de définir le statut non null.

j'ai trouvé que faire toute la chose dans une déclaration verrouiller une de nos tables les plus actives pendant 4-8 minutes et très souvent j'ai tué le processus. Cette méthode ne prend généralement que quelques secondes et provoque un verrouillage minimal.

en outre, si vous avez une table dans la zone des milliards de lignes il peut être intéressant de mettre à jour la mise à jour comme ceci:

WHILE 1=1
BEGIN
    UPDATE TOP (1000000) {schemaName}.{tableName}
        SET {columnName} = {DefaultValue}
        WHERE {columName} IS NULL

    IF @@ROWCOUNT < 1000000
        BREAK;
END
11
répondu Ste Bov 2017-05-26 22:29:56
la source

ajouter une nouvelle colonne à un tableau:

ALTER TABLE [table]
ADD Column1 Datatype

par exemple,

ALTER TABLE [test]
ADD ID Int

si l'utilisateur veut le rendre auto incrémenté alors:

ALTER TABLE [test]
ADD ID Int IDENTITY(1,1) NOT NULL
10
répondu Chirag Thakar 2017-05-26 22:21:41
la source
--Adding Value with Default Value
ALTER TABLE TestTable
ADD ThirdCol INT NOT NULL DEFAULT(0)
GO
10
répondu wild coder 2018-03-27 10:38:06
la source

Cela peut être fait par le code ci-dessous.

CREATE TABLE TestTable
    (FirstCol INT NOT NULL)
    GO
    ------------------------------
    -- Option 1
    ------------------------------
    -- Adding New Column
    ALTER TABLE TestTable
    ADD SecondCol INT
    GO
    -- Updating it with Default
    UPDATE TestTable
    SET SecondCol = 0
    GO
    -- Alter
    ALTER TABLE TestTable
    ALTER COLUMN SecondCol INT NOT NULL
    GO
9
répondu Mohit Dagar 2017-05-26 22:23:03
la source
ALTER TABLE tbl_table ADD int_column int NOT NULL DEFAULT(0)

à partir de cette requête, vous pouvez ajouter une colonne de type de données entier avec la valeur par défaut 0.

8
répondu Sandeep Kumar 2016-05-24 14:20:56
la source

eh Bien, j'ai maintenant quelques modifications à ma réponse précédente. J'ai remarqué qu'aucune des réponses mentionné IF NOT EXISTS . Donc, je vais fournir une nouvelle solution de celui-ci que j'ai fait face à certains problèmes modifiant la table.

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.columns WHERE table_name = 'TaskSheet' AND column_name = 'IsBilledToClient')
BEGIN
ALTER TABLE dbo.TaskSheet ADD
 IsBilledToClient bit NOT NULL DEFAULT ((1))
END
GO

ici TaskSheet est le nom de la table particulière et IsBilledToClient est la nouvelle colonne que vous allez insérer et 1 la valeur par défaut. Cela signifie dans la nouvelle colonne quelle sera la valeur de l'existant lignes, donc on sera définie automatiquement. Cependant, vous pouvez changer comme vous le souhaitez avec le respect du type de colonne comme j'ai utilisé BIT , donc j'ai mis la valeur par défaut 1.

je suggère le système ci-dessus, parce que j'ai fait face à un problème. Quel est donc le problème? Le problème est que, si la colonne IsBilledToClient existe dans la table table, alors si vous n'exécutez que la partie du code indiquée ci-dessous, vous verrez une erreur dans le constructeur de requêtes SQL server. Mais si c' n'existe pas, alors pour la première fois il n'y aura pas d'erreur lors de l'exécution.

ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
[WITH VALUES]
8
répondu gdmanandamohon 2017-05-26 22:26:55
la source

si la valeur par défaut est nulle, alors:

  1. dans SQL Server, ouvrez l'arbre de la table visée
  2. clic droit "colonnes" = = > New Column
  3. tapez le nom de la colonne, Select Type , et cochez la case "Allow Nulls"."
  4. dans la barre de Menu, cliquez sur Save

fait!

7
répondu usefulBee 2017-01-02 23:07:02
la source

SQL Server + Alter Table + Add Column + Default Value uniqueidentifier...

ALTER TABLE [TABLENAME] ADD MyNewColumn INT not null default 0 GO
6
répondu Chanukya 2015-12-07 13:44:35
la source