Ответ 1
К сожалению, вы не можете.
Если вы действительно хотите их в таком порядке, вам придется создать новую таблицу со столбцами в этом порядке и скопировать данные. Или переименовать столбцы и т.д. Нет простого способа.
Как добавить столбец в TSQL после определенного столбца?
У меня есть таблица:
MyTable
ID
FieldA
FieldB
Я хочу изменить таблицу и добавить столбец, чтобы он выглядел так:
MyTable
ID
NewField
FieldA
FieldB
В MySQL я бы так:
ALTER TABLE MyTable ADD COLUMN NewField int NULL AFTER ID;
Одна линия, хорошая, простая, отлично работает. Как это сделать в мире Microsoft?
Ответы
Ответ 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 для этого, вы должны отменить выбор следующего параметра, позволяющего удалить таблицу:
