webentwicklung-frage-antwort-db.com.de

Wie füge ich eine Spalte in SQL nach einer bestimmten Spalte hinzu?

Ich habe einen Tisch:

MyTable
    ID
    FieldA
    FieldB

Ich möchte die Tabelle ändern und eine Spalte hinzufügen, sodass es so aussieht:

MyTable
    ID
    NewField
    FieldA
    FieldB

In MySQL würde ich so ein:

ALTER TABLE MyTable ADD COLUMN NewField int NULL AFTER ID;

Eine Zeile, nett, einfach, funktioniert super. Wie mache ich das in der Welt von Microsoft?

32
Justin808

Leider kannst du nicht.

Wenn Sie sie wirklich in dieser Reihenfolge wünschen, müssen Sie eine neue Tabelle mit den Spalten in dieser Reihenfolge erstellen und die Daten kopieren. Oder benennen Sie Spalten um usw. Es gibt keinen einfachen Weg.

31
Mike M.

lösung:

Dies funktioniert für Tabellen, bei denen es keine Abhängigkeiten von der Änderungstabelle gibt, die kaskadierende Ereignisse auslösen würden. Stellen Sie zunächst sicher, dass Sie die Tabelle, die Sie umstrukturieren möchten, ohne katastrophale Auswirkungen löschen können. Notieren Sie sich alle Abhängigkeiten und Spalteneinschränkungen, die mit Ihrer Tabelle verbunden sind (z. B. Trigger, Indizes usw.). . Möglicherweise müssen Sie sie wieder einsetzen, wenn Sie fertig sind.

STEP 1: erstellt die temporäre Tabelle, die alle Datensätze der Tabelle enthält, die Sie umstrukturieren möchten. Vergessen Sie nicht, die neue Spalte aufzunehmen.

CREATE TABLE #tmp_myTable
(   [new_column] [int] NOT NULL, <-- new column has been inserted here!
    [idx] [bigint] NOT NULL,
    [name] [nvarchar](30) NOT NULL,
    [active] [bit] NOT NULL
)

SCHRITT 2: Vergewissern Sie sich, dass alle Datensätze kopiert wurden und die Spaltenstruktur so aussieht, wie Sie es möchten.

SELECT TOP 10 * FROM #tmp_myTable ORDER BY 1 DESC-- Sie können COUNT (*) oder alles tun, um sicherzustellen, dass Sie alle Datensätze kopiert haben

SCHRITT 3: DROP die Originaltabelle:

DROP TABLE myTable

Wenn Sie paranoid sind und schlechte Dinge passieren können, benennen Sie einfach die ursprüngliche Tabelle um (anstatt sie zu löschen). Auf diese Weise kann es immer zurückgegeben werden.

EXEC sp_rename myTable, myTable_Copy

SCHRITT 4: Erstellen Sie die Tabelle myTable wie gewünscht (sollte mit der Tabellenstruktur #tmp_myTable übereinstimmen.)

CREATE TABLE myTable
(   [new_column] [int] NOT NULL,
    [idx] [bigint] NOT NULL,
    [name] [nvarchar](30) NOT NULL,
    [active] [bit] NOT NULL
)

- Vergessen Sie nicht die Einschränkungen, die Sie benötigen

SCHRITT 5: Kopieren Sie alle Datensätze aus der temporären Tabelle #tmp_myTable in die neue (verbesserte) Tabelle myTable.

INSERT INTO myTable ([new_column],[idx],[name],[active])
SELECT [new_column],[idx],[name],[active]
FROM #tmp_myTable

SCHRITT 6: Prüfen Sie, ob sich alle Daten in Ihrer neuen, verbesserten Tabelle myTable befinden. Wenn ja, bereinigen Sie nach sich selbst und löschen Sie die temporäre Tabelle #tmp_myTable und die myTable_Copy -Tabelle, wenn Sie sie umbenennen anstatt sie zu löschen.

6
Milan

Dies sollte möglich sein, wenn Sie die Spalte über die GUI in Management Studio erstellen. Ich glaube, dass Management Studio den Tisch tatsächlich komplett neu erstellt, weshalb dies scheinbar geschieht.

Wie andere bereits erwähnt haben, spielt die Reihenfolge der Spalten in einer Tabelle keine Rolle. Wenn dies der Fall ist, stimmt der Code nicht.

3
Abe Miessler

/ * Skript zum Ändern der Spaltenreihenfolge einer Tabelle
Beachten Sie, dass dadurch eine neue Tabelle erstellt wird, die die ursprüngliche Tabelle ersetzt
Allerdings werden die Trigger oder andere Tabelleneigenschaften nicht kopiert - nur die Daten
* /

Erzeugen Sie eine neue Tabelle mit den Spalten in der gewünschten Reihenfolge.

Select Column2, Column1, Column3 Into NewTable from OldTable

Lösche die Originaltabelle

Drop Table OldTable;

Benennen Sie die neue Tabelle um

EXEC sp_rename 'NewTable', 'OldTable';

2
Glyn

Öffnen Sie in SQL Enterprise Management Studio Ihre Tabelle, fügen Sie die gewünschte Spalte hinzu und generieren Sie das Änderungsskript, anstatt die Änderung zu speichern. Sie können sehen, wie es in SQL gemacht wird.

Kurz gesagt, was andere gesagt haben, ist richtig. SQL Management Studio zieht alle Ihre Daten in eine temporäre Tabelle, löscht die Tabelle, erstellt sie mit Spalten in der richtigen Reihenfolge und gibt die temporären Tabellendaten dort wieder ein. Es gibt keine einfache Syntax, um eine Spalte an einer bestimmten Position hinzuzufügen.

1
user2124444

Sie müssen die Tabelle neu aufbauen. Zum Glück spielt die Reihenfolge der Spalten überhaupt keine Rolle!

Schau zu, wie ich deine Kolumnen magisch neu anordne:

SELECT ID, Newfield, FieldA, FieldB FROM MyTable

Auch das ist schon mal über eine Bazillion mal gefragt worden.

1
JNK

Gehen Sie in Microsoft SQL Server Management Studio (das Verwaltungstool für MSSQL) einfach in "Design" für eine Tabelle und ziehen Sie die Spalte an die neue Position. Keine Befehlszeile, aber Sie können es tun.

1
paparazzo

Das ist absolut möglich. Sie sollten es zwar nicht tun, es sei denn, Sie wissen, womit Sie es zu tun haben. Ich brauchte ungefähr zwei Tage, um es herauszufinden. Hier ist eine gespeicherte Prozedur, in die ich Folgendes eingebe: --- Datenbankname (Schemaname ist "_" zur besseren Lesbarkeit) --- Tabellenname --- Spalte .__ ---- Spaltendatentyp (hinzugefügte Spalte ist immer null, andernfalls können Sie die Position der neuen Spalte nicht einfügen.) ---.

Da ich mit Tabellen aus dem SAM-Toolkit arbeite (und einige davon mehr als 80 Spalten haben), kann die typische Variable die Abfrage nicht enthalten. Das zwingt zur externen Datei. Seien Sie nun vorsichtig, wo Sie diese Datei speichern und wer auf NTFS- und Netzwerkebene Zugriff hat.

Prost!

USE [master]
GO
/****** Object:  StoredProcedure [SP_Set].[TrasferDataAtColumnLevel]    Script Date: 8/27/2014 2:59:30 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [SP_Set].[TrasferDataAtColumnLevel]
(
    @database varchar(100),
    @table varchar(100),
    @column varchar(100),
    @position int,
    @datatype varchar(20)    
)
AS
BEGIN
set nocount on
exec  ('
declare  @oldC varchar(200), @oldCDataType varchar(200), @oldCLen int,@oldCPos int
create table Test ( dummy int)
declare @columns varchar(max) = ''''
declare @columnVars varchar(max) = ''''
declare @columnsDecl varchar(max) = ''''
declare @printVars varchar(max) = ''''

DECLARE MY_CURSOR CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR 
select column_name, data_type, character_maximum_length, ORDINAL_POSITION  from ' + @database + '.INFORMATION_SCHEMA.COLUMNS where table_name = ''' + @table + '''
OPEN MY_CURSOR FETCH NEXT FROM MY_CURSOR INTO @oldC, @oldCDataType, @oldCLen, @oldCPos WHILE @@FETCH_STATUS = 0 BEGIN

if(@oldCPos = ' + @position + ')
begin
    exec(''alter table Test add [' + @column + '] ' + @datatype + ' null'')
end

if(@oldCDataType != ''timestamp'')
begin

    set @columns += @oldC + '' , '' 
    set @columnVars += ''@'' + @oldC + '' , ''

    if(@oldCLen is null)
    begin
        if(@oldCDataType != ''uniqueidentifier'')
        begin
            set @printVars += '' print convert('' + @oldCDataType + '',@'' + @oldC + '')'' 
            set @columnsDecl += ''@'' + @oldC + '' '' + @oldCDataType + '', '' 
            exec(''alter table Test add ['' + @oldC + ''] '' + @oldCDataType + '' null'')
        end
        else
        begin
            set @printVars += '' print convert(varchar(50),@'' + @oldC + '')'' 
            set @columnsDecl += ''@'' + @oldC + '' '' + @oldCDataType + '', '' 
            exec(''alter table Test add ['' + @oldC + ''] '' + @oldCDataType + '' null'')
        end
    end
    else
    begin 
        if(@oldCLen < 0)
        begin
            set @oldCLen = 4000
        end
        set @printVars += '' print @'' + @oldC 
        set @columnsDecl += ''@'' + @oldC + '' '' + @oldCDataType + ''('' + convert(character,@oldCLen) + '') , '' 
        exec(''alter table Test add ['' + @oldC + ''] '' + @oldCDataType + ''('' + @oldCLen + '') null'')
    end
end

if exists (select column_name from INFORMATION_SCHEMA.COLUMNS where table_name = ''Test'' and column_name = ''dummy'')
begin
    alter table Test drop column dummy
end

FETCH NEXT FROM MY_CURSOR INTO  @oldC, @oldCDataType, @oldCLen, @oldCPos END CLOSE MY_CURSOR DEALLOCATE MY_CURSOR

set @columns = reverse(substring(reverse(@columns), charindex('','',reverse(@columns)) +1, len(@columns)))
set @columnVars = reverse(substring(reverse(@columnVars), charindex('','',reverse(@columnVars)) +1, len(@columnVars)))
set @columnsDecl = reverse(substring(reverse(@columnsDecl), charindex('','',reverse(@columnsDecl)) +1, len(@columnsDecl)))
set @columns = replace(replace(REPLACE(@columns, ''       '', ''''), char(9) + char(9),'' ''), char(9), '''')
set @columnVars = replace(replace(REPLACE(@columnVars, ''       '', ''''), char(9) + char(9),'' ''), char(9), '''')
set @columnsDecl = replace(replace(REPLACE(@columnsDecl, ''  '', ''''), char(9) + char(9),'' ''),char(9), '''')
set @printVars = REVERSE(substring(reverse(@printVars), charindex(''+'',reverse(@printVars))+1, len(@printVars))) 

create table query (id int identity(1,1), string varchar(max))

insert into query values  (''declare '' + @columnsDecl + ''
DECLARE MY_CURSOR CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR '')

insert into query values   (''select '' + @columns + '' from ' + @database + '._.' + @table + ''')

insert into query values  (''OPEN MY_CURSOR FETCH NEXT FROM MY_CURSOR INTO '' + @columnVars + '' WHILE @@FETCH_STATUS = 0 BEGIN '')

insert into query values   (@printVars )

insert into query values   ( '' insert into Test ('')
insert into query values   (@columns) 
insert into query values   ( '') values ( '' + @columnVars + '')'')

insert into query values  (''FETCH NEXT FROM MY_CURSOR INTO  '' + @columnVars + '' END CLOSE MY_CURSOR DEALLOCATE MY_CURSOR'')

declare @path varchar(100) = ''C:\query.sql''
declare @query varchar(500) = ''bcp "select string from query order by id" queryout '' + @path + '' -t, -c -S  '' + @@servername +  '' -T''

exec master..xp_cmdshell @query

set @query  = ''sqlcmd -S '' + @@servername + '' -i '' + @path

EXEC xp_cmdshell  @query

set @query = ''del ''  + @path

exec xp_cmdshell @query

drop table ' + @database + '._.' + @table + '

select * into ' + @database + '._.' + @table + ' from Test 

drop table query
drop table Test  ')

ENDE

0

Selbst wenn die Frage zu alt ist, wäre eine genauere Antwort zu Management Studio erforderlich.

Sie können die Spalte manuell oder mit Management Studio erstellen. Management Studio muss jedoch die Tabelle neu erstellen und führt zu einer Zeitüberschreitung, wenn bereits zu viele Daten vorhanden sind. Vermeiden Sie, wenn die Tabelle nicht hell ist.

Um die Reihenfolge der Spalten zu ändern, müssen Sie sie einfach in Management Studio verschieben. Dies sollte nicht erforderlich sein (Ausnahmen bestehen höchstwahrscheinlich), dass Management Studio die Tabelle neu erstellt, da die Reihenfolge der Spalten in den Tabellendefinitionen wahrscheinlich geändert wird.

Ich habe es bei zahlreichen Gelegenheiten mit Tabellen so gemacht, dass ich aufgrund der Daten in der GUI keine Spalten hinzufügen konnte. Anschließend wurden die Spalten mit der GUI von Management Studio verschoben und einfach gespeichert.

Sie gehen von einem gesicherten Timeout zu einigen Sekunden Wartezeit.

0
Rv3