server版本 - 将具有默认值的列添加到SQL Server中的现有表




sql server版本 (20)

句法:

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

例:

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.

笔记:

可选约束名称:
如果省略CONSTRAINT D_SomeTable_SomeCol则SQL Server将自动生成
一个有趣名称的默认DF__SomeTa__SomeC__4FB7FEF6如: DF__SomeTa__SomeC__4FB7FEF6

可选的With-Values声明:
仅当您的Column为Nullable时才需要WITH VALUES
并且您想要用于现有记录的默认值。
如果Column为NOT NULL ,则它将自动使用默认值
对于所有现有记录,无论您是否指定WITH VALUES

插件如何使用默认约束:
如果将Record插入SomeTable并且指定SomeCol的值,则默认为0
如果您插入Record 指定SomeCol的值为NULL (并且您的列允许空值),
然后将使用Default-Constraint,并且将插入NULL作为Value。

笔记基于以下每个人的好评。
特别感谢:
@Yatrix,@ WalterStabosz,@ YahooSerious和@StackMan的评论。

如何将具有默认值的列添加到SQL Server 2000 / SQL Server 2005中的现有表中?


SQL Server + Alter表+添加列+默认值uniqueidentifier ...

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

首先创建一个名为student的表:

CREATE TABLE STUDENT (STUDENT_ID INT NOT NULL)

添加一列:

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

SELECT * 
FROM STUDENT

将创建该表,并使用默认值将列添加到现有表中。


使用:

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

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

例:

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

例:

ALTER TABLE tes 
ADD ssd  NUMBER   DEFAULT '0';

在SQL Server 2008-R2中,我进入设计模式 - 在测试数据库中 - 使用设计器添加我的两列并使用GUI进行设置,然后臭名昭着的右键单击提供选项“ 生成更改脚本 ” !

Bang up弹出一个小窗口,您猜对了,正确格式化的保证工作更改脚本。 按下简单按钮。


好吧,我现在对我以前的答案进行了一些修改。 我注意到没有提到的答案IF NOT EXISTS 。 所以我将提供一个新的解决方案,因为我遇到了一些改变表格的问题。

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

这里TaskSheet是特定的表名, IsBilledToClient是您要插入的新列, 1是默认值。 这意味着在新列中将是现有行的值,因此将在那里自动设置一个。 但是,你可以像我使用BIT一样按照你想要的方式进行更改,所以我输入默认值1。

我建议上面的系统,因为我遇到了一个问题。 那么问题是什么? 问题是,如果表中的IsBilledToClient列确实存在,那么如果只执行下面给出的代码部分,则会在SQL Server“查询”构建器中看到错误。 但如果它不存在那么第一次执行时就不会有错误。

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

如果默认值为Null,则:

  1. 在SQL Server中,打开目标表的树
  2. 右键单击“列”==> New Column
  3. 键入列名称, Select Type ,然后选中允许空值复选框
  4. 从菜单栏中,单击“ Save

完成!


您可以通过以下方式使用T-SQL执行此操作。

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

您还可以通过右键单击“设计”菜单中的表来使用SQL Server Management Studio ,将默认值设置为表。

此外,如果要将相同的列(如果它不存在)添加到数据库中的所有表,则使用:

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

最基本的版本只有两行

ALTER TABLE MyTable
ADD MyNewColumn INT NOT NULL DEFAULT 0

添加可空列时WITH VALUES将确保将特定的DEFAULT值应用于现有行:

ALTER TABLE table
ADD column BIT     -- Demonstration with NULL-able column added
CONSTRAINT Constraint_name DEFAULT 0 WITH VALUES

试试这个

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

请注意,要添加的列具有NOT NULL约束,但没有DEFAULT约束(值)。 如果表中包含任何行,则ALTER TABLE语句将失败。 解决方案是从新列中删除NOT NULL约束,或为其提供DEFAULT约束。


这可以通过以下代码完成。

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

这有很多答案,但我觉得需要添加这个扩展方法。 这看起来要长得多,但如果你在一个活动数据库中有数百万行的表中添加一个NOT NULL字段,它就非常有用。

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

这样做是将列添加为可空字段并使用默认值,将所有字段更新为默认值(或者您可以指定更有意义的值),最后它将列更改为NOT NULL。

这样做的原因是,如果您更新一个大型表并添加一个新的非空字段,它必须写入每一行,因此会在添加列时锁定整个表,然后写入所有值。

此方法将添加可自然操作的可为空的列,然后在设置非空状态之前填充数据。

我发现在一个语句中完成整个操作会锁定一个更活跃的表,持续4-8分钟,而且我经常杀死这个过程。 这种方法每个部分通常只需几秒钟,并导致最小的锁定。

此外,如果您在数十亿行的区域中有一个表,则可能需要对更新进行批处理,如下所示:

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

    IF @@ROWCOUNT < 1000000
        BREAK;
END

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


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

包含DEFAULT会使用默认值填充现有行中的列,因此不会违反NOT NULL约束。


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

从此查询中,您可以添加一个数据类型为整数的列,其默认值为0。







sql-server-2000