sql-server server教學 - 將具有默認值的列添加到SQL Server中的現有表





server是什麼 server免費 (25)


這可以通過以下代碼完成。

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

如何將具有默認值的列添加到SQL Server 2000 / SQL Server 2005中的現有表中?




向表中添加新列:

ALTER TABLE [table]
ADD Column1 Datatype

例如,

ALTER TABLE [test]
ADD ID Int

如果用戶想要使其自動遞增,則:

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



例:

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



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



首先創建一個名為student的表:

CREATE TABLE STUDENT (STUDENT_ID INT NOT NULL)

添加一列:

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

SELECT * 
FROM STUDENT

將創建該表,並使用默認值將列添加到現有表中。




如果默認值為Null,則:

  1. 在SQL Server中,打開目標表的樹
  2. 右鍵單擊“列”==> New Column
  3. 鍵入列名稱, Select Type ,然後選中允許空值複選框
  4. 從菜單欄中,單擊“ Save

完成!




請注意,要添加的列具有NOT NULL約束,但沒有DEFAULT約束(值)。 如果表中包含任何行,則ALTER TABLE語句將失敗。 解決方案是從新列中刪除NOT NULL約束,或為其提供DEFAULT約束。




SQL Server + Alter表+添加列+默認值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 MyTable
ADD MyNewColumn INT NOT NULL DEFAULT 0



這有很多答案,但我覺得需要添加這個擴展方法。 這看起來要長得多,但如果你在一個活動數據庫中有數百萬行的表中添加一個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



這也可以在SSMS GUI中完成。 我在下面顯示默認日期,但當然默認值可以是任何值。

  1. 將您的表放在設計視圖中(右鍵單擊object explorer-> Design中的表)
  2. 向表中添加一列(如果已存在,則單擊要更新的列)
  3. 在下面的列屬性中,在默認值或綁定字段中輸入(getdate())abc0或任何值,如下圖所示:




例:

ALTER TABLE tes 
ADD ssd  NUMBER   DEFAULT '0';



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



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



您可以通過以下方式使用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 Protocols
ADD ProtocolTypeID int NOT NULL DEFAULT(1)
GO

包含DEFAULT會使用默認值填充現有行中的列,因此不會違反NOT NULL約束。




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



如果要添加多個列,可以這樣做,例如:

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



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

從此查詢中,您可以添加一個數據類型為整數的列,其默認值為0。




或者,您可以添加默認值而無需明確命名約束:

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

如果在創建此約束時遇到現有默認約束的問題,則可以通過以下方式刪除它們:

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



使用:

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

參考: ALTER TABLE(Transact-SQL) (MSDN)




使用:

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

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



對於Oracle 11g,我可以按如下方式更改列屬性:

ALTER TABLE tablename MODIFY columnname datatype NOT NULL;

否則,abatichev的答案看起來不錯。 你不能重複更改 - 它抱怨(至少在SQL Developer中)該列已經不是空的。







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