Agregar una columna con un valor predeterminado a una tabla existente en SQL Server
Cómo se puede agregar una columna con un valor predeterminado a una tabla existente en SQL Server 2000 / SQL Server 2005 ?
30 answers
Sintaxis:
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES
Ejemplo:
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.
Notas:
Nombre de restricción opcional:
Si omite CONSTRAINT D_SomeTable_SomeCol
entonces SQL Server generará automáticamente
un Contraint por defecto con un nombre divertido como: DF__SomeTa__SomeC__4FB7FEF6
Instrucción opcional With-Values:
El WITH VALUES
solo es necesario cuando su columna es Nullable
y desea que el Valor predeterminado utilizado para los Registros Existentes.
Si su columna es NOT NULL
, entonces usará automáticamente el Valor Predeterminado
para todos los Registros existentes, especifique WITH VALUES
o no.
Cómo funcionan las inserciones con una restricción predeterminada:
Si inserta un registro en SomeTable
y hace no Especifique el valor de SomeCol
, luego será por defecto 0
.
Si inserta un Registro y Especifique el valor de SomeCol
como NULL
(y su columna permite valores nulos),
a continuación, la restricción predeterminadano se utilizará y NULL
se insertará como el Valor.
Las notas se basaron en los excelentes comentarios de todos a continuación.
Un agradecimiento especial a:
@Yatrix, @ WalterStabosz, @YahooSerious, y @StackMan por sus comentarios.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-07-03 20:44:15
ALTER TABLE Protocols
ADD ProtocolTypeID int NOT NULL DEFAULT(1)
GO
La inclusión de DEFAULT llena la columna en filas existentes con el valor predeterminado, por lo que no se viola la restricción NOT NULL.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-04-19 13:35:13
Al agregar una columna nullable, WITH VALUES
se asegurará de que el valor PREDETERMINADO específico se aplique a las filas existentes:
ALTER TABLE table
ADD column BIT -- Demonstration with NULL-able column added
CONSTRAINT Constraint_name DEFAULT 0 WITH VALUES
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-26 19:11:03
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-07-31 03:46:24
ALTER TABLE MYTABLE ADD MYNEWCOLUMN VARCHAR(200) DEFAULT 'SNUGGLES'
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-11-16 23:33:24
Tenga cuidado cuando la columna que está agregando tiene una restricción NOT NULL
, pero no tiene una restricción DEFAULT
(valor). La instrucción ALTER TABLE
fallará en ese caso si la tabla tiene filas. La solución es eliminar la restricción NOT NULL
de la nueva columna o proporcionar una restricción DEFAULT
para ella.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2008-09-24 16:03:25
La versión más básica con solo dos líneas
ALTER TABLE MyTable
ADD MyNewColumn INT NOT NULL DEFAULT 0
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-05-25 14:50:21
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-26 19:10:35
Si desea agregar varias columnas, puede hacerlo de esta manera, por ejemplo:
ALTER TABLE YourTable
ADD Column1 INT NOT NULL DEFAULT 0,
Column2 INT NOT NULL DEFAULT 1,
Column3 VARCHAR(50) DEFAULT 'Hello'
GO
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-11-14 20:54:08
Uso:
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
Referencia: ALTER TABLE (Transact-SQL) (MSDN)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-26 19:18:28
Puede hacer lo siguiente con T-SQL.
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
Además de usar SQL Server Management Studio también puede hacer clic con el botón derecho en tabla en el menú Diseño, estableciendo el valor predeterminado en tabla.
Y además, si desea agregar la misma columna (si no existe) a todas las tablas de la base de datos, use:
USE AdventureWorks;
EXEC sp_msforeachtable
'PRINT ''ALTER TABLE ? ADD Date_Created DATETIME DEFAULT GETDATE();''' ;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-26 19:15:40
En SQL Server 2008-R2, voy al modo de diseño-en una base de datos de prueba-y añado mis dos columnas usando el diseñador e hice la configuración con la GUI, y luego el infame Clic derecho da la opción "Generar Script de Cambios"!
Bang up aparece una pequeña ventana con, lo adivinaste, el script de cambios con el formato correcto garantizado para funcionar. Presiona el botón fácil.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-26 19:17:22
Alternativamente, puede agregar un valor predeterminado sin tener que nombrar explícitamente la restricción:
ALTER TABLE [schema].[tablename] ADD DEFAULT ((0)) FOR [columnname]
Si tiene un problema con las restricciones predeterminadas existentes al crear esta restricción, puede eliminarlas:
alter table [schema].[tablename] drop constraint [constraintname]
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-26 19:19:09
Para agregar una columna a una tabla de base de datos existente con un valor predeterminado, podemos usar:
ALTER TABLE [dbo.table_name]
ADD [Column_Name] BIT NOT NULL
Default ( 0 )
Aquí hay otra forma de agregar una columna a una tabla de base de datos existente con un valor predeterminado.
Un script SQL mucho más completo para agregar una columna con un valor predeterminado está a continuación, incluyendo verificar si la columna existe antes de agregarla, también verificar la restricción y soltarla si hay una. Este script también nombra la restricción para que podamos tener una convención de nomenclatura agradable (me gusta DF_) y si not SQL nos dará una restricción con un nombre que tiene un número generado aleatoriamente; por lo que es bueno poder nombrar la restricción también.
-------------------------------------------------------------------------
-- 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
Estas son dos formas de agregar una columna a una tabla de base de datos existente con un valor predeterminado.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-11-29 19:13:11
ALTER TABLE ADD ColumnName {Column_Type} Constraint
El artículo de MSDN MODIFICAR TABLA (Transact-SQL) tiene toda la sintaxis alter table.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-07-29 21:11:17
Ejemplo:
ALTER TABLE [Employees] ADD Seniority int not null default 0 GO
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-07-12 06:39:13
Esto también se puede hacer en la GUI de SSMS. Muestro una fecha predeterminada a continuación, pero el valor predeterminado puede ser lo que sea, por supuesto.
- Ponga su tabla en la vista de diseño (haga clic derecho sobre la tabla en el objeto explorador->Diseño)
- Agregue una columna a la tabla (o haga clic en la columna que desea actualizar si ya existe)
- En las propiedades de la columna a continuación, ingrese
(getdate())
oabc
o0
o el valor que desee en el campo Valor predeterminado o Enlace como se muestra en la imagen abajo:
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-07-16 15:36:51
Ejemplo:
ALTER TABLE tes
ADD ssd NUMBER DEFAULT '0';
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-04-15 05:13:58
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))
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-07-07 08:38:01
Primero crea una tabla con el nombre student:
CREATE TABLE STUDENT (STUDENT_ID INT NOT NULL)
Añádele una columna:
ALTER TABLE STUDENT
ADD STUDENT_NAME INT NOT NULL DEFAULT(0)
SELECT *
FROM STUDENT
Se crea la tabla y se agrega una columna a una tabla existente con un valor predeterminado.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-29 08:28:06
Prueba esto
ALTER TABLE Product
ADD ProductID INT NOT NULL DEFAULT(1)
GO
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-09-01 07:17:52
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-06-21 11:29:50
Esto tiene muchas respuestas, pero siento la necesidad de agregar este método extendido. Esto parece mucho más largo, pero es extremadamente útil si está agregando un campo NOT NULL a una tabla con millones de filas en una base de datos activa.
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
Lo que hará esto es agregar la columna como un campo nullable y con el valor predeterminado, actualizar todos los campos al valor predeterminado (o puede asignar valores más significativos), y finalmente cambiará la columna para que NO sea NULL.
La razón de esto es si actualiza una tabla de gran escala y agrega un nuevo campo no nulo, tiene que escribir en cada fila y, por lo tanto, bloqueará toda la tabla a medida que agrega la columna y luego escribe todos los valores.
Este método agregará la columna nullable que funciona mucho más rápido por sí misma, luego rellena los datos antes de establecer el estado not null.
He encontrado que hacer todo en una declaración bloqueará una de nuestras mesas más activas durante 4-8 minutos y muy a menudo he matado proceso. Este método cada parte generalmente toma solo unos segundos y causa un bloqueo mínimo.
Además, si tiene una tabla en el área de miles de millones de filas, puede valer la pena agrupar la actualización de la siguiente manera:
WHILE 1=1
BEGIN
UPDATE TOP (1000000) {schemaName}.{tableName}
SET {columnName} = {DefaultValue}
WHERE {columName} IS NULL
IF @@ROWCOUNT < 1000000
BREAK;
END
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-26 19:29:56
Añadir una nueva columna a una tabla:
ALTER TABLE [table]
ADD Column1 Datatype
Por ejemplo,
ALTER TABLE [test]
ADD ID Int
Si el usuario quiere que se incremente automáticamente entonces:
ALTER TABLE [test]
ADD ID Int IDENTITY(1,1) NOT NULL
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-26 19:21:41
--Adding Value with Default Value
ALTER TABLE TestTable
ADD ThirdCol INT NOT NULL DEFAULT(0)
GO
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-03-27 07:38:06
Esto se puede hacer con el siguiente código.
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-26 19:23:03
ALTER TABLE tbl_table ADD int_column int NOT NULL DEFAULT(0)
Desde esta consulta puede agregar una columna de tipo de datos entero con el valor predeterminado 0.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-05-24 11:20:56
Bueno, ahora tengo algunas modificaciones a mi respuesta anterior. He notado que ninguna de las respuestas mencionadas IF NOT EXISTS
. Así que voy a ofrecer una nueva solución de la misma, ya que me he enfrentado a algunos problemas que alteran la tabla.
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
Aquí TaskSheet
está el nombre particular de la tabla y IsBilledToClient
es la nueva columna que va a insertar y 1
el valor predeterminado. Eso significa que en la nueva columna cuál será el valor de las filas existentes, por lo tanto una se establecerá automáticamente allí. Sin embargo, usted puede cambiar como desee con respecto al tipo de columna como he usado BIT
, por lo que pongo el valor predeterminado 1.
Sugiero el sistema anterior, porque he enfrentado un problema. Entonces, ¿cuál es el problema? El problema es que si la columna IsBilledToClient
existe en la tabla, si ejecuta solo la parte del código que se muestra a continuación, verá un error en el generador de consultas de SQL Server. Pero si no existe entonces por primera vez no habrá error al ejecutar.
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
[WITH VALUES]
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-26 19:26:55
Si el valor predeterminado es Null, entonces:
- En SQL Server, abra el árbol de la tabla de destino
- Haga clic derecho en" Columnas " = = >
New Column
- Escriba el nombre de la columna,
Select Type
, y Marque la casilla Permitir nulos - En la Barra de menús, haga clic en
Save
Hecho!
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-01-02 20:07:02
SQL Server + Alter Table + Add Column + Default Value uniqueidentifier...
ALTER TABLE [TABLENAME] ADD MyNewColumn INT not null default 0 GO
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-12-07 10:44:35