Pytanie Jak zmienić wiele kolumn jednocześnie w SQL Server


potrzebuję ALTER typy danych z kilku kolumn w tabeli.

W przypadku pojedynczej kolumny następujące działa dobrze:

ALTER TABLE tblcommodityOHLC
ALTER COLUMN
    CC_CommodityContractID NUMERIC(18,0) 

Ale jak mogę zmienić wiele kolumn w jednej instrukcji? Poniższe nie działa:

ALTER TABLE tblcommodityOHLC
ALTER COLUMN
    CC_CommodityContractID NUMERIC(18,0), 
    CM_CommodityID NUMERIC(18,0)

87
2017-08-12 07:27


pochodzenie


Jaka jest postrzegana korzyść z robienia tego za jednym razem? - onedaywhen
@onedaywhen - Aby SQL Server po prostu przechodził przez tabelę, aby wykonać konieczne sprawdzanie poprawności wobec nowego typu danych i / lub zapisywanie zmienionych kolumn w nowym formacie. - Martin Smith
Bez większych korzyści! - onedaywhen
Przeciwnie. Byłoby wielką zaletą mieć przebieg zmian w ciągu 2 godzin zamiast 24 dla wielu kolumn na dużych stołach. - Norbert Kardos


Odpowiedzi:


To jest niemożliwe. Będziesz musiał to zrobić jeden po drugim.

Możesz utworzyć tymczasową tabelę ze zmodyfikowanymi kolumnami, skopiować dane przez cały czas, upuścić oryginalny stół i zmienić nazwę tabeli tymczasowej na oryginalną nazwę.


94
2017-08-12 07:30



+1, You will need to do this one by one., więc, co jest wielka sprawa, po prostu użyj wielu ALTER TABLE ALTER COLUMN polecenia? - KM.
@KM Jednym z problemów jest zmiana dużego stołu. Każda instrukcja oznacza nowy skan, ale jeśli można zmienić wiele kolumn, wszystkie zmiany mogłyby być znacznie szybsze - erikkallen
@erikkallen, następnie wykonaj, jak narzędzia SSMS zwykle generują swoje skrypty: utwórz nową tabelę i replikuj FK i indeksy, itd., upuść oryginalną tabelę, a następnie zmień nazwę nowej tabeli, - KM.
Upuszczanie i odtwarzanie tabel to dość intensywna operacja. Domyślnie jest teraz wyłączony w SSMS i prawdopodobnie bez ważnego powodu. - jocull


Wykonanie wielu operacji KOLUMNA ALTER w jednej instrukcji ALTER TABLE nie jest możliwy.

Zobacz tutaj składnia ALTER TABLE:
http://msdn.microsoft.com/en-US/library/ms190273.aspx

Możesz zrobić wiele ADD lub wiele KOLUMNY DROP, ale tylko jeden ALTER KOLUMNA.


17
2017-07-05 03:54





Poniższe rozwiązanie nie jest pojedynczą instrukcją do zmiany wielu kolumn, ale tak, ułatwia to życie:

  1. Wygeneruj tabelę CREATE scenariusz.

  2. Zastąpić CREATE TABLE z ALTER TABLE [TableName] ALTER COLUMN dla pierwszej linii

  3. Usuń niechciane kolumny z listy.

  4. Zmień typy danych kolumn, jak chcesz.

  5. Wykonaj a Znajdź i zamień… następująco:

    1. Odnaleźć: NULL,
    2. Zamienić: NULL; ALTER TABLE [TableName] ALTER COLUMN
    3. Trafienie Zastąpić przycisk.
  6. Uruchom skrypt.

Mam nadzieję, że zaoszczędzi to dużo czasu :))


5
2018-01-24 09:27





Jak inni odpowiedzieli, potrzebujesz wielu instrukcji ALTER TABLE. Próbować:

ALTER TABLE tblcommodityOHLC alter column CC_CommodityContractID NUMERIC(18,0);
ALTER TABLE tblcommodityOHLC alter column CM_CommodityID NUMERIC(18,0);

itp.


4
2017-08-10 21:02





Jak powiedziało wiele osób, będziesz musiał użyć wielu ALTER COLUMN instrukcje, po jednej dla każdej kolumny, którą chcesz zmodyfikować.

Jeśli chcesz zmodyfikować wszystkie lub kilka kolumn w tabeli na ten sam typ danych (np. Rozwijając pole VARCHAR z 50 znaków na 100 znaków), możesz wygenerować wszystkie instrukcje automatycznie, korzystając z poniższego zapytania. Ta technika jest również przydatna, jeśli chcesz zastąpić ten sam znak w wielu polach (np. Usunięcie \ t ze wszystkich kolumn).

SELECT
     TABLE_CATALOG
    ,TABLE_SCHEMA
    ,TABLE_NAME
    ,COLUMN_NAME
    ,'ALTER TABLE ['+TABLE_SCHEMA+'].['+TABLE_NAME+'] ALTER COLUMN ['+COLUMN_NAME+'] VARCHAR(300)' as 'code'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'your_table' AND TABLE_SCHEMA = 'your_schema'

Generuje to ALTER TABLE oświadczenie dla każdej kolumny dla ciebie.


2
2017-07-26 15:32





Jeśli wykonasz zmiany w studio zarządzania i wygenerujesz skrypty, utworzy on nową tabelę i wstawi do niej stare dane ze zmienionymi typami danych. Oto mały przykład zmieniający typy danych dwóch kolumn

/*
   12 August 201008:30:39
   User: 
   Server: CLPPRGRTEL01\TELSQLEXPRESS
   Database: Tracker_3
   Application: 
*/

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.tblDiary
    DROP CONSTRAINT FK_tblDiary_tblDiary_events
GO
ALTER TABLE dbo.tblDiary_events SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_tblDiary
    (
    Diary_ID int NOT NULL IDENTITY (1, 1),
    Date date NOT NULL,
    Diary_event_type_ID int NOT NULL,
    Notes varchar(MAX) NULL,
    Expected_call_volumes real NULL,
    Expected_duration real NULL,
    Skill_affected smallint NULL
    )  ON T3_Data_2
     TEXTIMAGE_ON T3_Data_2
GO
ALTER TABLE dbo.Tmp_tblDiary SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_tblDiary ON
GO
IF EXISTS(SELECT * FROM dbo.tblDiary)
     EXEC('INSERT INTO dbo.Tmp_tblDiary (Diary_ID, Date, Diary_event_type_ID, Notes, Expected_call_volumes, Expected_duration, Skill_affected)
        SELECT Diary_ID, Date, Diary_event_type_ID, CONVERT(varchar(MAX), Notes), Expected_call_volumes, Expected_duration, CONVERT(smallint, Skill_affected) FROM dbo.tblDiary WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_tblDiary OFF
GO
DROP TABLE dbo.tblDiary
GO
EXECUTE sp_rename N'dbo.Tmp_tblDiary', N'tblDiary', 'OBJECT' 
GO
ALTER TABLE dbo.tblDiary ADD CONSTRAINT
    PK_tblDiary PRIMARY KEY NONCLUSTERED 
    (
    Diary_ID
    ) WITH( PAD_INDEX = OFF, FILLFACTOR = 86, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON T3_Data_2

GO
CREATE UNIQUE CLUSTERED INDEX tblDiary_ID ON dbo.tblDiary
    (
    Diary_ID
    ) WITH( PAD_INDEX = OFF, FILLFACTOR = 86, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON T3_Data_2
GO
CREATE NONCLUSTERED INDEX tblDiary_date ON dbo.tblDiary
    (
    Date
    ) WITH( PAD_INDEX = OFF, FILLFACTOR = 86, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON T3_Data_2
GO
ALTER TABLE dbo.tblDiary WITH NOCHECK ADD CONSTRAINT
    FK_tblDiary_tblDiary_events FOREIGN KEY
    (
    Diary_event_type_ID
    ) REFERENCES dbo.tblDiary_events
    (
    Diary_event_ID
    ) ON UPDATE  CASCADE 
     ON DELETE  CASCADE 

GO
COMMIT

1
2017-08-12 07:33





select 'ALTER TABLE ' + OBJECT_NAME(o.object_id) + 
    ' ALTER COLUMN ' + c.name + ' DATETIME2 ' + 
    CASE WHEN c.is_nullable = 0 THEN 'NOT NULL' ELSE 'NULL' END
from sys.objects o
inner join sys.columns c on o.object_id = c.object_id
inner join sys.types t on c.system_type_id = t.system_type_id
where o.type='U'
and c.name = 'Timestamp'
and t.name = 'datetime'
order by OBJECT_NAME(o.object_id)

dzięki uprzejmości devio


0
2018-02-20 07:38





Dzięki przykładowi kodu Evan udało mi się go zmodyfikować i uzyskać bardziej specyficzny dla tabel zaczynających się od, konkretnych nazw kolumn i szczegółów obsługi dla ograniczeń. Uruchomiłem ten kod, a następnie skopiowałem kolumnę [CODE] i wykonałem ją bez problemu.

USE [Table_Name]
GO
SELECT
     TABLE_CATALOG
    ,TABLE_SCHEMA
    ,TABLE_NAME
    ,COLUMN_NAME
    ,DATA_TYPE
    ,'ALTER TABLE ['+TABLE_SCHEMA+'].['+TABLE_NAME+'] DROP CONSTRAINT [DEFAULT_'+TABLE_NAME+'_'+COLUMN_NAME+']; 
ALTER TABLE  ['+TABLE_SCHEMA+'].['+TABLE_NAME+'] ALTER COLUMN ['+COLUMN_NAME+'] datetime2 (7) NOT NULL 
ALTER TABLE  ['+TABLE_SCHEMA+'].['+TABLE_NAME+'] ADD CONSTRAINT [DEFAULT_'+TABLE_NAME+'_'+COLUMN_NAME+'] DEFAULT (''3/6/2018 6:47:23 PM'') FOR ['+COLUMN_NAME+']; 
GO' AS '[CODE]'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE 'form_%' AND TABLE_SCHEMA = 'dbo'
 AND (COLUMN_NAME = 'FormInserted' OR COLUMN_NAME = 'FormUpdated')
 AND DATA_TYPE = 'datetime'

0
2018-04-02 17:36





Możemy zmienić wiele kolumn w jednym zapytaniu, tak jak poniżej:

ALTER TABLE `tblcommodityOHLC`
    CHANGE COLUMN `updated_on` `updated_on` DATETIME NULL DEFAULT NULL AFTER `updated_by`,
    CHANGE COLUMN `delivery_datetime` `delivery_datetime` DATETIME NULL DEFAULT CURRENT_TIMESTAMP AFTER `delivery_status`;

Po prostu podaj zapytania jako oddzielone przecinkami.


-1
2018-05-08 06:25



Myślę, że to jest MySql? Pytanie dotyczyło SQL Server, a to nie działa na serwerze sql - Tjipke


- Utwórz tabelę tymczasową     UTWÓRZ TABELę temp_table_alter     (     column_name varchar (255)
    );

- wstaw te coulmeny do tabeli temp, dla których możemy zmienić wielkość kolumn

INSERT INTO temp_table_alter (column_name) VALUES ('colm1');
INSERT INTO temp_table_alter (column_name) VALUES ('colm2');
INSERT INTO temp_table_alter (column_name) VALUES ('colm3');
INSERT INTO temp_table_alter (column_name) VALUES ('colm4');

DECLARE @col_name_var varchar(255);
DECLARE alter_table_cursor CURSOR FOR
select column_name from temp_table_alter ;

OPEN alter_table_cursor
FETCH NEXT FROM alter_table_cursor INTO @col_name_var
WHILE @@FETCH_STATUS = 0
 BEGIN

 PRINT('ALTER COLUMN ' + @col_name_var);
 EXEC ('ALTER TABLE Original-table  ALTER COLUMN ['+ @col_name_var + '] DECIMAL(11,2);')

 FETCH NEXT FROM alter_table_cursor INTO @col_name_var
 END

CLOSE alter_table_cursor

WYZWÓL: alter_table_cursor

- na końcu tabeli temperatury kropli      zrzut tabeli temp_table_alter;


-2
2018-03-13 15:42



NIE dobre rozwiązanie. Kursorów i pętli należy unikać za wszelką cenę !!! - Ray K.
Nie prawda, Ray. Kursory i pętle są odpowiednie dla niektórych zadań DDL i innych zadań RBR. - Jeff Moden


Jeśli dobrze zrozumiałem twoje pytanie, możesz dodać wiele kolumn w tabeli, używając poniższego zapytania.

Pytanie:

Alter table tablename add (column1 dataype, column2 datatype);

-2
2017-07-14 10:36



OP zapytał o kolumnę ALTER, a nie ADD. - D.R.