Dodaj kolumnę z wartością domyślną do istniejącej tabeli w SQL Server
Jak można dodać kolumnę z wartością domyślną do istniejącej tabeli w SQL Server 2000 / SQL Server 2005 ?
30 answers
Składnia:
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES
Przykład:
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.
Uwagi:
Opcjonalna Nazwa Ograniczenia:
Jeśli pominiesz CONSTRAINT D_SomeTable_SomeCol
, SQL Server automatycznie wygeneruje
Default-Contraint o śmiesznej nazwie jak: DF__SomeTa__SomeC__4FB7FEF6
Opcjonalna Instrukcja With-Values: WITH VALUES
jest potrzebne tylko wtedy, gdy Twoja kolumna jest Nullable
i chcesz, aby wartość domyślna była używana dla istniejących rekordów.
Jeśli Twoja kolumna to NOT NULL
, to automatycznie użyje wartość domyślna
dla wszystkich istniejących rekordów, niezależnie od tego, czy podasz WITH VALUES
, czy nie.
Jak Wstawki działają z domyślnym ograniczeniem:
Jeśli wstawisz rekord do SomeTable
i zrobisz Nie podaj wartość SomeCol
, wtedy domyślnie będzie to 0
.
Jeśli wstawisz rekord oraz podaj wartość SomeCol
jako NULL
(a twoja kolumna pozwala na null),
wtedy default-Constraint będzie Nie być używane i NULL
zostanie wstawiona jako wartość.
Uwagi zostały oparte na wspaniałych opiniach wszystkich poniżej.
Specjalne podziękowania dla:
@Yatrix, @ WalterStabosz, @ YahooSerious i @StackMan za komentarze.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-07-03 20:44:15
ALTER TABLE Protocols
ADD ProtocolTypeID int NOT NULL DEFAULT(1)
GO
Włączenie DEFAULT wypełnia kolumnę w istniejących wierszach wartością domyślną, więc ograniczenie NOT NULL nie jest naruszane.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-04-19 13:35:13
Podczas dodawania nullable column, WITH VALUES
zapewni, że konkretna wartość domyślna zostanie zastosowana do istniejących wierszy:
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/doraprojects.net/template/agent.layouts/content.php on line 54
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/doraprojects.net/template/agent.layouts/content.php on line 54
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/doraprojects.net/template/agent.layouts/content.php on line 54
2012-11-16 23:33:24
Uważaj, gdy dodawana kolumna ma NOT NULL
ograniczenie, ale nie ma DEFAULT
ograniczenie (wartość). Instrukcja ALTER TABLE
nie powiedzie się w takim przypadku, jeśli w tabeli znajdują się wiersze. Rozwiązaniem jest albo usunięcie ograniczenia NOT NULL
z nowej kolumny, albo podanie ograniczenia DEFAULT
.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2008-09-24 16:03:25
Najbardziej podstawowa wersja tylko z dwoma liniami
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/doraprojects.net/template/agent.layouts/content.php on line 54
2012-05-25 14:50:21
Użycie:
-- 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/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-26 19:10:35
Jeśli chcesz dodać kilka kolumn, możesz to zrobić w ten sposób na przykład:
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/doraprojects.net/template/agent.layouts/content.php on line 54
2017-11-14 20:54:08
Użycie:
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
Reference: 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/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-26 19:18:28
Możesz zrobić to z T-SQL w następujący sposób.
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
Jak również możesz użyć SQL Server Management Studio również klikając prawym przyciskiem myszy tabelę w menu Projekt, ustawiając domyślną wartość tabeli.
A ponadto, jeśli chcesz dodać tę samą kolumnę (jeśli nie istnieje) do wszystkich tabel w bazie danych, użyj:
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/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-26 19:15:40
W SQL Server 2008-R2 przechodzę do trybu projektowania-w testowej bazie danych-i dodaję moje dwie kolumny za pomocą projektanta i dokonuję ustawień za pomocą GUI, a następnie infamous prawym przyciskiem myszy daje opcję "Wygeneruj skrypt zmiany"!
Bang up wyskakuje małe okienko z, jak się domyślacie, poprawnie sformatowanym skryptem zmiany gwarantowanej pracy. Naciśnij przycisk "easy".
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-26 19:17:22
Alternatywnie można dodać wartość domyślną bez konieczności jawnego nazywania ograniczenia:
ALTER TABLE [schema].[tablename] ADD DEFAULT ((0)) FOR [columnname]
Jeśli masz problem z istniejącymi domyślnymi ograniczeniami podczas tworzenia tego ograniczenia, można je usunąć przez:
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/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-26 19:19:09
Aby dodać kolumnę do istniejącej tabeli bazy danych z wartością domyślną, możemy użyć:
ALTER TABLE [dbo.table_name]
ADD [Column_Name] BIT NOT NULL
Default ( 0 )
Oto inny sposób na dodanie kolumny do istniejącej tabeli bazy danych z wartością domyślną.
Znacznie dokładniejszy skrypt SQL, aby dodać kolumnę z wartością domyślną, znajduje się poniżej, w tym sprawdzenie, czy kolumna istnieje przed dodaniem, a także sprawdzenie ograniczenia i upuszczenie go, jeśli istnieje. Ten skrypt również nazywa ograniczenie, więc możemy mieć ładną konwencję nazewnictwa (Lubię DF_) i jeśli nie SQL da nam ograniczenie z nazwą, która ma losowo wygenerowany numer; więc miło jest też móc nazwać ograniczenie.
-------------------------------------------------------------------------
-- 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
Są to dwa sposoby dodania kolumny do istniejącej tabeli bazy danych z wartością domyślną.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-11-29 19:13:11
ALTER TABLE ADD ColumnName {Column_Type} Constraint
Artykuł MSDN ALTER TABLE (Transact-SQL) posiada całą składnię tabeli alter.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-07-29 21:11:17
Przykład:
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/doraprojects.net/template/agent.layouts/content.php on line 54
2014-07-12 06:39:13
Można to zrobić również w GUI SSMS. Poniżej pokazuję domyślną datę, ale domyślną wartością może być oczywiście Dowolna.
- Umieść tabelę w widoku Projekt (kliknij prawym przyciskiem myszy tabelę w obiekcie explorer - > Design)
- Dodaj kolumnę do tabeli (lub kliknij kolumnę, którą chcesz zaktualizować, jeśli już istnieje)
- we właściwościach kolumny poniżej wprowadź
(getdate())
LUBabc
lub0
lub dowolną wartość w polu Domyślna wartość lub powiązanie Jak Na zdjęciu poniżej:
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-07-16 15:36:51
Przykład:
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/doraprojects.net/template/agent.layouts/content.php on line 54
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/doraprojects.net/template/agent.layouts/content.php on line 54
2015-07-07 08:38:01
Najpierw Utwórz tabelę o nazwie student:
CREATE TABLE STUDENT (STUDENT_ID INT NOT NULL)
Dodaj do niego jedną kolumnę:
ALTER TABLE STUDENT
ADD STUDENT_NAME INT NOT NULL DEFAULT(0)
SELECT *
FROM STUDENT
Tabela zostanie utworzona i do istniejącej tabeli zostanie dodana kolumna z wartością domyślną.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-29 08:28:06
Spróbuj tego
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/doraprojects.net/template/agent.layouts/content.php on line 54
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/doraprojects.net/template/agent.layouts/content.php on line 54
2016-06-21 11:29:50
To ma wiele odpowiedzi, ale czuję potrzebę dodania tej rozszerzonej metody. Wydaje się to o wiele dłuższe, ale jest niezwykle przydatne, jeśli dodajesz pole NOT NULL do tabeli z milionami wierszy w aktywnej bazie danych.
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
Spowoduje to dodanie kolumny jako pola nullable i z wartością domyślną zaktualizuje wszystkie pola do wartości domyślnej( lub możesz przypisać bardziej znaczące wartości), a na koniec zmieni kolumnę na nie NULL.
Powodem tego jest jeśli zaktualizujesz tabelę o dużej skali i dodasz nowe pole not null, musi ona zapisywać do każdego wiersza, a tym samym zablokuje całą tabelę podczas dodawania kolumny, a następnie zapisuje wszystkie wartości.
Ta metoda doda kolumnę nullable, która sama działa dużo szybciej, a następnie wypełni dane przed ustawieniem statusu not null.
Odkryłem, że robienie całej rzeczy w jednym oświadczeniu zablokuje jeden z naszych bardziej aktywnych stołów na 4-8 minut i dość często zabijałem proces. Ta metoda każda część zwykle zajmuje tylko kilka sekund i powoduje minimalne zablokowanie.
Dodatkowo, jeśli masz tabelę w obszarze miliardów wierszy, może warto dodać aktualizację w ten sposób:
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/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-26 19:29:56
Dodaj nową kolumnę do Tabeli:
ALTER TABLE [table]
ADD Column1 Datatype
Na przykład,
ALTER TABLE [test]
ADD ID Int
Jeśli użytkownik chce ją automatycznie zwiększyć, to:
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/doraprojects.net/template/agent.layouts/content.php on line 54
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/doraprojects.net/template/agent.layouts/content.php on line 54
2018-03-27 07:38:06
Można to zrobić za pomocą poniższego kodu.
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/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-26 19:23:03
ALTER TABLE tbl_table ADD int_column int NOT NULL DEFAULT(0)
Z tego zapytania można dodać kolumnę datatype integer z domyślną wartością 0.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-05-24 11:20:56
Cóż, mam teraz kilka modyfikacji do mojej poprzedniej odpowiedzi. Zauważyłem, że żadna z odpowiedzi nie została wymieniona IF NOT EXISTS
. Więc mam zamiar dostarczyć nowe rozwiązanie, ponieważ miałem do czynienia z pewnymi problemami zmieniając tabelę.
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
Tutaj TaskSheet
jest konkretną nazwą tabeli i {[4] } jest nową kolumną, którą chcesz wstawić i 1
wartością domyślną. To znaczy w nowej kolumnie jaka będzie wartość istniejących wierszy, dlatego zostanie tam ustawiony automatycznie. Jednak Ty można zmienić, jak chcesz w odniesieniu do typu kolumny jak użyłem BIT
, więc umieścić w domyślnej wartości 1.
Proponuję powyższy system, ponieważ napotkałem problem. Więc w czym problem? Problem polega na tym, że jeśli kolumna IsBilledToClient
istnieje w tabeli Tabela, to jeśli wykonasz tylko część kodu podanego poniżej, zobaczysz błąd w konstruktorze zapytań SQL server. Jeśli jednak nie istnieje, to po raz pierwszy nie wystąpi błąd podczas wykonywania.
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/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-26 19:26:55
Jeśli wartością domyślną jest Null, to:
- w SQL Server otwórz drzewo docelowej tabeli
- Kliknij prawym przyciskiem myszy "Columns" = = >
New Column
- wpisz nazwę kolumny,
Select Type
i zaznacz pole wyboru Zezwól na Nulls - z paska Menu Kliknij
Save
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
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/doraprojects.net/template/agent.layouts/content.php on line 54
2015-12-07 10:44:35