sql-server column - Aggiungere una colonna con un valore predefinito a una tabella esistente in SQL Server



come select (25)

Sintassi:

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

Esempio:

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.

Gli appunti:

Nome vincolo facoltativo:
Se si CONSTRAINT D_SomeTable_SomeCol SQL Server si autogenererà
un DF__SomeTa__SomeC__4FB7FEF6 con un nome divertente come: DF__SomeTa__SomeC__4FB7FEF6

Dichiarazione facoltativa con valori:
WITH VALUES è necessario solo quando la tua colonna è Nullable
e si desidera il valore predefinito utilizzato per i record esistenti.
Se la tua colonna NOT NULL è NOT NULL , allora utilizzerà automaticamente il valore predefinito
per tutti i record esistenti, indipendentemente dal fatto che si specifichi WITH VALUES o meno.

In che modo gli inserti funzionano con un vincolo predefinito:
Se si inserisce un record in SomeTable e non si specifica il valore di SomeCol , verrà SomeCol su 0 .
Se si inserisce un record e si specifica il valore di SomeCol come NULL (e la colonna consente i valori null),
quindi il vincolo predefinito non verrà utilizzato e NULL verrà inserito come valore.

Le note erano basate sull'ottimo feedback di tutti di seguito.
Ringraziamenti speciali a:
@Yatrix, @WalterStabosz, @YahooSerious e @StackMan per i loro commenti.

Come può essere aggiunta una colonna con un valore predefinito a una tabella esistente in SQL Server 2000 / SQL Server 2005 ?


Questo può essere fatto anche nella GUI SSMS. Io mostro una data di default sotto, ma il valore di default può essere qualunque cosa, ovviamente.

  1. Metti la tua tabella in visualizzazione disegno (fai clic con il tasto destro del mouse su object explorer-> Design)
  2. Aggiungi una colonna alla tabella (o fai clic sulla colonna che vuoi aggiornare se già esiste)
  3. In Proprietà colonna di seguito, immettere (getdate()) o abc o 0 o qualsiasi valore desiderato nel campo Valore predefinito o Binding , come illustrato di seguito:


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

Per prima cosa crea una tabella con nome studente:

CREATE TABLE STUDENT (STUDENT_ID INT NOT NULL)

Aggiungi una colonna ad esso:

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

SELECT * 
FROM STUDENT

La tabella viene creata e una colonna viene aggiunta a una tabella esistente con un valore predefinito.


In alternativa, puoi aggiungere un valore predefinito senza dover esplicitamente denominare il vincolo:

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

Se si verifica un problema con i vincoli predefiniti esistenti durante la creazione di questo vincolo, possono essere rimossi da:

alter table [schema].[tablename] drop constraint [constraintname]

SQL Server + Modifica tabella + Aggiungi colonna + Valore predefinito uniqueidentifier

ALTER TABLE Product 
ADD ReferenceID uniqueidentifier not null 
default (cast(cast(0 as binary) as uniqueidentifier))

ALTER TABLE tbl_table ADD int_column int NOT NULL DEFAULT(0)

Da questa query è possibile aggiungere una colonna di un intero tipo di dati con il valore predefinito 0.


Aggiungi una nuova colonna a una tabella:

ALTER TABLE [table]
ADD Column1 Datatype

Per esempio,

ALTER TABLE [test]
ADD ID Int

Se l'utente vuole farlo incrementare automaticamente, allora:

ALTER TABLE [test]
ADD ID Int IDENTITY(1,1) NOT NULL

ALTER TABLE MYTABLE ADD MYNEWCOLUMN VARCHAR(200) DEFAULT 'SNUGGLES'

Esempio:

ALTER TABLE tes 
ADD ssd  NUMBER   DEFAULT '0';

ALTER TABLE Protocols
ADD ProtocolTypeID int NOT NULL DEFAULT(1)
GO

L'inclusione di DEFAULT riempie la colonna nelle righe esistenti con il valore predefinito, quindi il vincolo NOT NULL non viene violato.


Se il valore predefinito è Null, quindi:

  1. In SQL Server, apri l'albero della tabella di destinazione
  2. Fai clic con il tasto destro del mouse su "Colonne" ==> New Column
  3. Digitare la colonna Nome, Select Type e selezionare la casella di controllo Consenti null
  4. Dalla barra dei menu, fare Save su Save

Fatto!


Questo ha molte risposte, ma sento il bisogno di aggiungere questo metodo esteso. Questo sembra molto più lungo, ma è estremamente utile se si aggiunge un campo NOT NULL a una tabella con milioni di righe in un database attivo.

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

Ciò che questo farà è aggiungere la colonna come campo nullable e con il valore predefinito, aggiornare tutti i campi al valore predefinito (oppure assegnare valori più significativi) e alla fine cambierà la colonna in NOT NOT.

La ragione di ciò è che se si aggiorna una tabella di grandi dimensioni e si aggiunge un nuovo campo non nullo che deve scrivere su ogni singola riga, con la presente si bloccherà l'intera tabella man mano che si aggiunge la colonna e quindi si scrivono tutti i valori.

Questo metodo aggiungerà la colonna nullable che opera molto più velocemente da sola, quindi riempie i dati prima di impostare lo stato non nullo.

Ho scoperto che eseguire l'intera operazione in un'unica istruzione bloccherà uno dei nostri tavoli più attivi per 4-8 minuti e molto spesso ho ucciso il processo. Questo metodo richiede solitamente solo pochi secondi e causa un blocco minimo.

Inoltre, se si dispone di una tabella nell'area di miliardi di righe, potrebbe valere la pena di eseguire il batching dell'aggiornamento in questo modo:

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

    IF @@ROWCOUNT < 1000000
        BREAK;
END

La versione più semplice con solo due linee

ALTER TABLE MyTable
ADD MyNewColumn INT NOT NULL DEFAULT 0

Esempio:

ALTER TABLE [Employees] ADD Seniority int not null default 0 GO

Puoi fare la cosa con T-SQL nel modo seguente.

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

Oltre a poter utilizzare SQL Server Management Studio, puoi anche fare clic con il pulsante destro del mouse sul menu Design, impostando il valore predefinito sulla tabella.

Inoltre, se si desidera aggiungere la stessa colonna (se non esiste) a tutte le tabelle nel database, utilizzare:

 USE AdventureWorks;
 EXEC sp_msforeachtable
'PRINT ''ALTER TABLE ? ADD Date_Created DATETIME DEFAULT GETDATE();''' ;

Uso:

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

ALTER TABLE myTableName  
ADD RecordAddedDate smalldatetime NULL DEFAULT(GetDate())  
GO 

Bene, ora ho qualche modifica alla mia risposta precedente. Ho notato che nessuna delle risposte citate IF NOT EXISTS . Quindi ne fornirò una nuova soluzione poiché ho riscontrato alcuni problemi nell'alterare la tabella.

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

Qui TaskSheet è il nome della tabella particolare e IsBilledToClient è la nuova colonna che stai per inserire e 1 il valore predefinito. Ciò significa che nella nuova colonna quale sarà il valore delle righe esistenti, quindi verrà impostato automaticamente lì. Tuttavia, puoi cambiare come desideri con il rispetto del tipo di colonna come se avessi usato BIT , quindi inserisco il valore predefinito 1.

Suggerisco il sistema di cui sopra, perché ho avuto un problema. Quindi qual'è il problema? Il problema è che se la colonna IsBilledToClient esiste nella tabella tabella, se si esegue solo la parte del codice indicata di seguito, verrà visualizzato un errore nel generatore Query del server SQL. Ma se non esiste allora per la prima volta non ci saranno errori durante l'esecuzione.

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

Prova questo

ALTER TABLE Product
ADD ProductID INT NOT NULL DEFAULT(1)
GO

SQL Server + Modifica tabella + Aggiungi colonna + Valore predefinito uniqueidentifier ...

ALTER TABLE [TABLENAME] ADD MyNewColumn INT not null default 0 GO

--Adding Value with Default Value
ALTER TABLE TestTable
ADD ThirdCol INT NOT NULL DEFAULT(0)
GO

ALTER TABLE ADD ColumnName {Column_Type} Constraint

L'articolo MSDN ALTER TABLE (Transact-SQL) presenta tutta la sintassi di alter table.


Per aggiungere una colonna a una tabella di database esistente con un valore predefinito, possiamo usare:

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

Ecco un altro modo per aggiungere una colonna a una tabella di database esistente con un valore predefinito.

Uno script SQL molto più approfondito per aggiungere una colonna con un valore predefinito è al di sotto che include il controllo se la colonna esiste prima di aggiungerla, anche se si verifica il vincolo e lo si elimina se ce n'è uno. Questo script nomina anche il vincolo in modo che possiamo avere una bella convenzione di denominazione (mi piace DF_) e se non SQL ci darà un vincolo con un nome che ha un numero generato casualmente; quindi è bello poter nominare anche il vincolo.

-------------------------------------------------------------------------
-- 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

Questi sono due modi per aggiungere una colonna a una tabella di database esistente con un valore predefinito.


Se vuoi aggiungere più colonne puoi farlo in questo modo ad esempio:

ALTER TABLE YourTable
    ADD Column1 INT NOT NULL DEFAULT 0,
        Column2 INT NOT NULL DEFAULT 1,
        Column3 VARCHAR(50) DEFAULT 'Hello'
GO

--Questa è un'altra variante utilizzata per documentare un database di grandi dimensioni per la conversione (Modificato su --remove colonne statiche)

SELECT o.Name                   as Table_Name
     , c.Name                   as Field_Name
     , t.Name                   as Data_Type
     , t.length                 as Length_Size
     , t.prec                   as Precision_
FROM syscolumns c 
     INNER JOIN sysobjects o ON o.id = c.id
     LEFT JOIN  systypes t on t.xtype = c.xtype  
WHERE o.type = 'U' 
ORDER BY o.Name, c.Name

- Nel join sinistro, c.type viene sostituito da c.xtype per ottenere i tipi varchar





sql sql-server sql-server-2005 sql-server-2000