Добавить новый столбец таблицы в определенную порядковую позицию в Microsoft SQL Server
Можно ли добавить столбец к таблице в определенной порядковой позиции в Microsoft SQL Server?
Например, наши таблицы всегда имеют столбцы CreatedOn, CreatedBy, LastModifiedOn, LastModifiedBy в конце каждого определения таблицы? Я хотел бы, чтобы новый столбец отображался в SSMS над этими столбцами.
Если я пишу все изменения в моей базе данных, есть ли способ сохранить этот порядок в конце таблицы?
К вашему сведению, я не пытаюсь начать пламенную войну, если это вообще нужно сделать. Если вы хотите прочитать о потоке, который быстро вырождается в это, вот хороший:
http://www.developersdex.com/sql/message.asp?p=581&r=5014513
11 ответов
Вы должны создать временную таблицу, которая отражает схему исходной таблицы, но с желаемым порядком столбцов, а затем скопировать содержимое оригинала в temp. Удалите оригинал и переименуйте темп.
Это то, что SQL Management Studio делает за кулисами.
С помощью инструмента синхронизации схемы вы можете автоматически генерировать эти сценарии.
Зайдите в SQL Server Management Studio и "спроектируйте" существующую таблицу. Вставьте столбец в середину, щелкните правой кнопкой мыши в пустой области и выберите " Создать сценарий изменения"...
Теперь посмотрите на сценарий, который он создает. в основном он создает временную таблицу с правильным порядком столбцов, вставляет данные из исходной таблицы, удаляет исходную таблицу и переименовывает временную таблицу. Это, вероятно, то, что вам нужно сделать.
Вам также может понадобиться снять этот флажок, чтобы разрешить создание сценариев изменений
Ответ - да, это технически возможно, но при этом у вас будет болеть голова, и выполнение и настройка займет много времени.
Один: создание / копирование / удаление / переименование
Это фактически то, что SQL Server делает в графическом интерфейсе: вот пример сценария, который он генерирует и выполняет, когда вы нажимаете кнопку "Сохранить" после добавления нового столбца в начало таблицы.
/* 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
CREATE TABLE dbo.Tmp_SomeTable
(
MyNewColumn int NOT NULL,
OriginalIntColumn int NULL,
OriginalVarcharColumn varchar(100) NULL
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_SomeTable SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_SomeTable ON
GO
IF EXISTS(SELECT * FROM dbo.SomeTable)
EXEC('INSERT INTO dbo.Tmp_SomeTable (OriginalIntColumn, OriginalVarcharColumn FROM dbo.SomeTable WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_SomeTable OFF
GO
DROP TABLE dbo.SomeTable
GO
EXECUTE sp_rename N'dbo.Tmp_SomeTable', N'SomeTable', 'OBJECT'
GO
GO
COMMIT
Два: ДОБАВИТЬ КОЛОННУ / ОБНОВЛЕНИЕ / УДАЛИТЬ КОЛОННУ / ПЕРЕИМЕНОВАТЬ
Этот метод в основном включает в себя создание копии любых существующих столбцов, которые вы хотите добавить справа от вашего нового столбца, перенос данных в новый столбец, затем отбрасывание оригиналов и переименование новых. Это приведет к хаосу с любыми имеющимися у вас индексами или ограничениями, поскольку вам придется их переопределять. Это технически возможно, но опять же отнимает много времени как с точки зрения разработки, так и исполнения.
CREATE TABLE MyTest (a int, b int, d int, e int)
INSERT INTO MyTest (a,b,d,e) VALUES(1,2,4,5)
SELECT * FROM MyTest -- your current table
ALTER TABLE MyTest ADD c int -- add a new column
ALTER TABLE MyTest ADD d_new int -- create copies of the existing columns you want to move
ALTER TABLE MyTest ADD e_new int
UPDATE MyTest SET d_new = d, e_new = e -- transfer data to the new columns
ALTER TABLE MyTest DROP COLUMN d -- remove the originals
ALTER TABLE MyTest DROP COLUMN e
EXEC SP_RENAME 'MyTest.d_new', 'd'; -- rename the new columns
EXEC SP_RENAME 'MyTest.e_new', 'e';
SELECT * FROM MyTest
DROP TABLE MyTest -- clean up the sample
Три: жить с этим
Это сильно оскорбляет мое чувство порядка... но иногда это просто не стоит переставлять.
Насколько мне известно, нет никакого известного способа изменить порядок столбца. За кулисами SQL Management Studio делает то, что сказал Хосе Базилио. А если у вас большая таблица, то нецелесообразно менять порядок столбцов таким образом.
Вы можете использовать "вид". С SQL-представлениями вы можете использовать любой порядок, который вам нравится, без изменений столбца таблицы.
Я использую SSMS 18. Я просто сделал
- Открытый дизайн стола
- позиционирование требуемого столбца путем его перетаскивания
- И согласно ответу KM (второй в потоке) - снимите флажок, чтобы разрешить создание сценариев изменения, см. Изображение выше.
- Сохраните изменения.
- Готово. Проверьте свой стол сейчас.
То, что я считаю простым, это добавить столбец ALTER TABLE table1 ADD .., а затем создать таблицу tmp, такую как tmp_table1, из выбора, подобного SELECT col1,col2,col5,col3,col4 into tmp_table1 from table1; а затем удалите table1 и переименуйте tmp_table1 в table1, вот и все. Я надеюсь, что это поможет кому-то
Выберите все столбцы во временной таблице и создайте новую таблицу с новым столбцом, который вы хотите удалить из старой таблицы, выберите все столбцы из временной таблицы и вставьте в новый столбец с переупорядочением. и без потери данных
select * FROM TEMP
select * from originaltbl
select * from #Stagintbl
declare @ColumnName nvarchar(max);
set @ColumnName=(select
distinct
stuff((
select ',' + a.COLUMN_NAME
from (select Column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='originaltbl') a
for xml path('')
),1,1,'') as ColumnName)
declare @Sqlquery nvarchar(max)
set @Sqlquery='select '+@ColumnName+' from #Stagintbl'+'';
Insert into originaltbl
Execute(@Sqlquery)
TFS 2013 сделает это за вас автоматически.
Добавьте новый столбец (столбцы) в свою таблицу так, как вам нравится, и затем зафиксируйте ваши изменения в TFS. Оттуда вы можете открыть файл sql таблицы в Visual Studio и вручную переместить порядок столбцов в скрипте T-SQL CREATE. Затем вы можете обновить целевую базу данных с помощью инструмента сравнения схем VS, который находится в разделе "Инструменты"> "SQL Server"> "Сравнение новой схемы". Выберите ваш проект базы данных с вашим изменением в качестве источника и базу данных, которую вы хотите обновить в качестве цели. Сравните, выберите скрипт таблицы и обновите. VS опустится и добавится автоматически. Все ваши данные будут в безопасности, и индексы тоже.
Я не уверен, если поток все еще активен. У меня был тот же запрос с базой данных MySQL. Щелкните правой кнопкой мыши по таблице и выберите "ALTER", автоматически сгенерированный ниже код. Образец предоставлен sakila db и все заработало. Просто найдите столбец, после которого вы хотите разместить новый столбец, и используйте ключевое слово "AFTER".
ALTER TABLE `sakila`.`actor`
CHANGE COLUMN `middle_name` `middle_name` VARCHAR(50) NULL DEFAULT NULL AFTER `first_name`;
Вы не можете создать столбец в определенном порядковом месте в структуре таблицы - плюс, зачем вам это нужно?? Порядок столбцов в SQL Server совершенно не имеет значения.
Среда SQL Server Management Studio имеет конструкторское представление, которое, по-видимому, позволяет создавать столбцы в любом месте списка столбцов, но на самом деле в фоновом режиме происходит то, что создается новая таблица с новыми столбцами, а старая удаляется.,
Нет никаких команд SQL DDL для создания столбца в определенном месте или для "переупорядочивания" столбцов. Это действительно не нужно.
Марк
Грязно и просто.
Экспорт таблицы в CSV.
Вставьте новые данные в нужную позицию.
Дроп стол.
Создайте новую таблицу с нужными спецификациями столбцов.
Загрузить столбцы из CSV в новую таблицу.