Як додати стовпець у TSQL після певного стовпця?

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

MyTable
    ID
    FieldA
    FieldB

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

MyTable
    ID
    NewField
    FieldA
    FieldB

У MySQL я б так:

ALTER TABLE MyTable ADD COLUMN NewField int NULL AFTER ID;

Один рядок, приємний, простий, чудово працює. Як це зробити у світі Майкрософт?

24
Чи не це поле column_id у таблиці sys.columns (sql server)?
додано Автор ganders, джерело
додано Автор JNK, джерело

9 Відповіді

На жаль, ви не можете.

Якщо ви дійсно бажаєте їх у такому порядку, вам доведеться створити нову таблицю зі стовпцями у цьому порядку та скопіювати дані. Або перейменовувати стовпці тощо. Простий спосіб не існує.

24
додано
Ви просто перетягніть стовпчик в Management Studio. Це досить легко
додано Автор paparazzo, джерело
@ PreguntonCojoneroCabron Management Studio зберігає дані в тому сенсі, що він копіює його до тимчасового розташування, якщо йому потрібно зробити деструктивну зміну таблиці. Встановлення стовпця в певному порядку в таблиці - це одна така операція, де таблицю потрібно скинути та відтворити. SSMS копіює дані, скидає таблицю, відтворює таблицю з новою схемою, а потім знову додає дані до таблиці.
додано Автор squillman, джерело
@Peter Studio Studio має дані ?
додано Автор PreguntonCojoneroCabrón, джерело
Management Studio може знищити і відтворити стіл для вас, але якщо у вас є дуже величезний стіл з великою кількістю рядків, то краще просто змінити його. Було б добре, якби було це зробити без руйнування столу. Management Studio насправді має можливість припинити вас робити це, оскільки це може зайняти багато часу.
додано Автор Peter, джерело

рішення:

Це буде працювати для таблиць, де немає залежностей на зміненій таблиці, яка може викликати каскадні події. Спочатку переконайтеся, що ви можете скинути таблицю, яку ви хочете реструктурувати, без будь-яких катастрофічних наслідків. Зверніть увагу на всі пов'язані з вашою таблицею обмеження залежностей та стовпців (наприклад, тригери, індекси і т. д.). Вам може знадобитися повернути їх, коли ви закінчите.

STEP 1: create the temp table to hold all the records from the table you want to restructure. Do not forget to include the new column.

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
)

STEP 2: Make sure all records have been copied over and that the column structure looks the way you want.

SELECT TOP 10 * FROM #tmp_myTable ORDER BY 1 DESC -- you can do COUNT(*) or anything to make sure you copied all the records

STEP 3: DROP the original table:

DROP TABLE myTable

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

EXEC sp_rename myTable, myTable_Copy

STEP 4: Recreate the table myTable the way you want (should match match the #tmp_myTable table structure)

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

- не забувайте про будь-які обмеження, які вам можуть знадобитися

STEP 5: Copy the all the records from the temp #tmp_myTable table into the new (improved) table 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 , якщо ви вирішили перейменувати її, а не опустити її.

4
додано

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

Як інші згадували, порядок стовпців у таблиці не має значення, і якщо він містить щось не так з вашим кодом.

3
додано

/*   Сценарій для зміни порядку стовпця таблиці
  Зверніть увагу, що буде створено нову таблицю, яка замінить оригінальну таблицю
  АЛЕ, це не копіює тригери та інші властивості таблиці - просто дані
  */

Створити нову таблицю зі стовпцями у тому порядку, що вам потрібно

Виберіть Column2, Column1, Column3 InTable у OldTable

Видалити оригінальну таблицю

Drop Table OldTable;

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

EXEC sp_rename 'NewTable', 'OldTable';

2
додано

У Microsoft SQL Server Management Studio (інструмент адміністрування для MSSQL) просто перейдіть до "дизайну" на столі та перетягніть стовпчик до нової позиції. Не командний рядок, але ви можете це зробити.

1
додано

Вам доведеться перебудувати стіл. На щастя, порядок стовпців взагалі не має значення!

Дивіться, як я магічно змінюю ваші стовпці:

SELECT ID, Newfield, FieldA, FieldB FROM MyTable

Також це було запитано про час, який базильон раніше.

1
додано
добре, це може не бути, якщо я маю доступ до нього через SQL безпосередньо, але я ні. Крім того, я хотів би, щоб всі мої таблиці були налаштовані однаково (оновлення та нові створені).
додано Автор Justin808, джерело
Порядок стовпців з'являється в студії керування. На дуже великих таблицях замовлення може бути важливим для конвенції.
додано Автор Peter, джерело

У SQL Enterprise Management Studio відкрийте таблицю, додайте стовпець там, де ви хочете, а потім - замість збереження змін - створюйте скрипт зміни. Ви можете подивитися, як це робиться в SQL.

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

1
додано

Навіть якщо питання старі, то потрібно буде точніше про Management Studio.

Ви можете створити колонку вручну або за допомогою Management Studio. Але Management Studio вимагатиме відтворити стіл і призведе до вимкнення часу, якщо в ньому вже є забагато даних, якщо стіл не є світлим.

Щоб змінити порядок стовпців, вам просто потрібно перемістити їх в Management Studio. Це не повинно вимагати (з винятками, швидше за все, існує), що Management Studio відтворює таблицю, оскільки вона, швидше за все, змінює координати стовпців у визначенні таблиці.

Я зробив це таким чином на численних випадках з таблицями, що я не міг додати стовпці з графічним інтерфейсом через дані в них. Потім перемістивши колонки навколо з графічним інтерфейсом Management Studio і просто зберегли їх.

Ви перейдете з запевненого часу до кількох секунд очікування.

0
додано

Це абсолютно можливо. Хоча ви не повинні робити це, якщо не знаєте, з чим ви маєте справу. Мені потрібно близько 2 днів, щоб зрозуміти це. Ось процедура збереження, де я ввожу: --- ім'я бази даних (схема назва "_" для читабельності) --- ім'я таблиці --- стовпчик --- тип даних стовпця (стовпчик додається завжди нуль, інакше ви не зможете вставити) --- позиція нової колонки.

Since I'm working with tables from SAM toolkit (and some of them have > 80 columns) , the typical variable won't be able to contain the query. That forces the need of external file. Now be careful where you store that file and who has access on NTFS and network level.

Ура!

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

END

0
додано
Щось, що можна покращити щодо цієї процедури, можна додати значення за замовчуванням до нового стовпця, а потім розділити попередні стовпці на 2 частини: до і після нового стовпця. Коли ви вставляєте ці значення, ви можете додати "@beforeColumn + default + @ after column" у виділеному операторі вставки. Будь-ласка, запропонуйте інші покращення, якщо такі є :)
додано Автор Zlatin Todorinski, джерело