wake-up-neo.com

So ändern Sie mehrere Spalten gleichzeitig in SQL Server

Ich muss die Datentypen mehrerer Spalten in einer Tabelle ALTER.

Für eine einzelne Spalte funktioniert das Folgende gut:

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

Aber wie ändere ich mehrere Spalten in einer Anweisung? Folgendes funktioniert nicht:

ALTER TABLE tblcommodityOHLC
ALTER COLUMN
    CC_CommodityContractID NUMERIC(18,0), 
    CM_CommodityID NUMERIC(18,0)
99
D.mahesh

Das ist nicht möglich. Sie müssen dies nacheinander tun.

Sie können eine temporäre Tabelle mit Ihren geänderten Spalten erstellen, die Daten kopieren, Ihre ursprüngliche Tabelle löschen und Ihre temporäre Tabelle in Ihren ursprünglichen Namen umbenennen.

105
Neil Knight

Das Durchführen mehrerer ALTER COLUMN-Aktionen in einer einzigen ALTER TABLE-Anweisung ist nicht möglich.

Siehe die ALTER TABLE-Syntax hier:
http://msdn.Microsoft.com/en-US/library/ms190273.aspx

Sie können mehrere ADD oder mehrere DROP COLUMN, aber nur eine ALTER COLUMN.

17
Fernando Torres

Die folgende Lösung ist keine einzige Anweisung zum Ändern mehrerer Spalten, aber es macht das Leben einfach:

  1. Generieren Sie das CREATE-Skript einer Tabelle. 

  2. Ersetzen Sie CREATE TABLE durch ALTER TABLE [TableName] ALTER COLUMN für die erste Zeile 

  3. Entfernen Sie unerwünschte Spalten aus der Liste.

  4. Ändern Sie die Datentypen der Spalten wie gewünscht.

  5. Führen Sie ein Suchen und Ersetzen… wie folgt aus:

    1. Find: NULL,
    2. Ersetzen durch: NULL; ALTER TABLE [TableName] ALTER COLUMN
    3. Drücken Sie die Taste Replace .
  6. Führen Sie das Skript aus.

Hoffe es wird viel Zeit sparen :))

7
Pritam

Wie andere beantwortet haben, benötigen Sie mehrere ALTER TABLE-Anweisungen. Versuchen:

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

usw.

5
Ray K.

Wie viele andere schon gesagt haben, müssen Sie mehrere ALTER COLUMN-Anweisungen verwenden, eine für jede Spalte, die Sie ändern möchten.

Wenn Sie alle oder mehrere Spalten in Ihrer Tabelle auf denselben Datentyp ändern möchten (z. B. ein VARCHAR-Feld von 50 auf 100 Zeichen erweitern), können Sie alle Anweisungen automatisch mithilfe der folgenden Abfrage generieren. Diese Technik ist auch nützlich, wenn Sie dasselbe Zeichen in mehreren Feldern ersetzen möchten (z. B.\t aus allen Spalten entfernen).

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'

Dies generiert für Sie eine ALTER TABLE-Anweisung für jede Spalte.

2
Evan

Wenn Sie die Änderungen in Management Studio vornehmen und Skripts erstellen, wird eine neue Tabelle erstellt und die alten Daten mit den geänderten Datentypen eingefügt. Hier ist ein kleines Beispiel zum Ändern der Datentypen zweier Spalten

/*
   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
Kevin Ross

Dank des Codebeispiels von Evan konnte ich es mehr modifizieren und spezifischer für Tabellen machen, die mit spezifischen Spaltennamen UND Spezifika für Einschränkungen beginnen. Ich habe diesen Code ausgeführt, die [CODE] -Spalte kopiert und ohne Probleme ausgeführt.

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
Brenden Kehren
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)

mit freundlicher Genehmigung von devio

0
Spyder

Wenn Sie das Ganze nicht selbst schreiben und alle Spalten in denselben Datentyp ändern möchten, kann dies die Vereinfachung erleichtern:

select 'alter table tblcommodityOHLC alter column '+name+ 'NUMERIC(18,0);'
from syscolumns where id = object_id('tblcommodityOHLC ')

Sie können die Ausgabe als Abfrage kopieren und einfügen

0
Rishi