Как добавить столбец в TSQL после определенного столбца?

У меня есть таблица:

MyTable
    ID
    FieldA
    FieldB

Я хочу изменить таблицу и добавить столбец, чтобы он выглядел так:

MyTable
    ID
    NewField
    FieldA
    FieldB

В MySQL я бы так:

ALTER TABLE MyTable ADD COLUMN NewField int NULL AFTER ID;

Одна линия, хорошая, простая, отлично работает. Как это сделать в мире Microsoft?

Ответ 1

К сожалению, вы не можете.

Если вы действительно хотите их в таком порядке, вам придется создать новую таблицу со столбцами в этом порядке и скопировать данные. Или переименовать столбцы и т.д. Нет простого способа.

Ответ 2

Решение:

Это будет работать для таблиц, где нет зависимостей от таблицы изменения, которая могла бы вызвать каскадные события. Сначала убедитесь, что вы можете удалить таблицу, которую хотите реструктурировать, без каких-либо катастрофических последствий. Обратите внимание на все зависимости и ограничения столбцов, связанные с вашей таблицей (т.е. триггеры, индексы и т.д.). Возможно, вам придется вернуть их, когда закончите.

ШАГ 1: создать временную таблицу для хранения всех записей из таблицы, которую вы хотите реструктурировать. Не забудьте включить новый столбец.

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
)

ШАГ 2: Убедитесь, что все записи скопированы и структура столбцов выглядит так, как вы хотите.

SELECT TOP 10 * FROM #tmp_myTable ORDER BY 1 DESC - вы можете сделать COUNT (*) или что угодно, чтобы убедиться, что вы скопировали все записи

ШАГ 3: УБЕДИТЕ исходную таблицу:

DROP TABLE myTable

Если вы параноик о плохих вещах, возможно, просто переименуйте исходную таблицу (вместо ее удаления). Таким образом, он всегда может быть возвращен обратно.

EXEC sp_rename myTable, myTable_Copy

ШАГ 4: Восстановить таблицу myTable так, как вы хотите (должно соответствовать структуре таблицы #tmp_myTable)

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

- не забывайте о каких-либо ограничениях, которые могут вам понадобиться

ШАГ 5: Скопируйте все записи из таблицы temp #tmp_myTable в новую (улучшенную) таблицу myTable.

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

ШАГ 6: проверьте, вернулись ли все данные в новую улучшенную таблицу myTable. Если да, очистите после себя и DROP таблицу temp #tmp_myTable и таблицу myTable_Copy, если вы решили переименовать ее, а не отбрасывать ее.

Ответ 3

Вы должны это сделать, если создать столбец с помощью графического интерфейса в Management Studio. Я считаю, что студия управления фактически полностью воссоздает таблицу, поэтому это происходит.

Как уже упоминалось, порядок столбцов в таблице не имеет значения, и если он делает что-то не так с вашим кодом.

Ответ 4

/* Script, чтобы изменить порядок столбцов таблицы Обратите внимание, что это создаст новую таблицу для замены исходной таблицы
ОДНАКО, он не копирует триггеры или другие свойства таблицы - просто данные
*/

Создать новую таблицу с столбцами в том порядке, в котором вы нуждаетесь

Select Column2, Column1, Column3 Into NewTable from OldTable

Удалить исходную таблицу

Drop Table OldTable;

Переименовать новую таблицу

EXEC sp_rename 'NewTable', 'OldTable';

Ответ 5

Вам нужно перестроить таблицу. К счастью, порядок столбцов не имеет значения!

Наблюдайте за тем, как я магически изменяю ваши столбцы:

SELECT ID, Newfield, FieldA, FieldB FROM MyTable

Также об этом спрашивали о базиллионе раз.

Ответ 6

В Microsoft SQL Server Management Studio (административный инструмент для MSSQL) просто перейдите в "дизайн" на таблицу и перетащите столбец в новую позицию. Не командная строка, но вы можете это сделать.

Ответ 7

Даже если вопрос старый, потребуется более точный ответ относительно Management Studio.

Вы можете создать столбец вручную или с помощью Management Studio. Но Management Studio потребует пересоздания таблицы и приведет к превышению времени ожидания, если у вас уже слишком много данных, избегайте, если таблица не легкая.

Чтобы изменить порядок столбцов, вам просто нужно переместить их в Management Studio. Это не должно требовать (исключения, скорее всего, существуют), что Management Studio воссоздает таблицу, поскольку она, скорее всего, изменяет порядок расположения столбцов в определениях таблицы.

Я неоднократно делал это с таблицами, которые не могли добавить столбцы с графическим интерфейсом из-за данных в них. Затем переместили столбцы с помощью графического интерфейса Management Studio и просто сохранили их.

Вы перейдете от гарантированного времени до нескольких секунд ожидания.

Ответ 8

В SQL Enterprise Management Studio откройте таблицу, добавьте столбец, где вы хотите, а затем - вместо сохранения изменения - сгенерируйте изменение script. Вы можете увидеть, как это делается в SQL.

Короче говоря, то, что говорили другие, верно. Студия SQL Management позволяет вытащить все ваши данные в временную таблицу, отбрасывает таблицу, воссоздает ее со столбцами в правильном порядке и помещает данные таблицы temp туда. Нет простого синтаксиса для добавления столбца в определенную позицию.

Ответ 9

Это абсолютно возможно. Хотя вы не должны этого делать, если не знаете, с чем имеете дело. Принял меня около двух дней, чтобы понять это. Вот хранимая процедура, в которой я вхожу: --- имя базы данных (имя схемы "_" для удобства чтения) --- имя таблицы --- колонка --- тип данных столбца (добавленный столбец всегда равен нулю, иначе вы не сможете вставить) --- позиция нового столбца.

Поскольку я работаю с таблицами из инструментария SAM (а некоторые из них имеют > 80 столбцов), типичная переменная не сможет содержать запрос. Это вынуждает внешний файл. Теперь будьте осторожны, где вы храните этот файл и у кого есть доступ к NTFS и сетевому уровню.

Ура!

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  ')

КОНЕЦ

Ответ 10

Если вы используете GUI для этого, вы должны отменить выбор следующего параметра, позволяющего удалить таблицу: