如何在TSQL中在特定列后添加一列？

Question

如何在TSQL中在特定列后添加一列？

50

我有一张表：

MyTable
    ID
    FieldA
    FieldB

我想修改表格并添加一列，使其看起来像：

MyTable
    ID
    NewField
    FieldA
    FieldB

在MySQL中我会这样做：

ALTER TABLE MyTable ADD COLUMN NewField int NULL AFTER ID;

一行代码，简洁明了，非常好用。我该如何在微软的世界里做到这一点？

- Justin808

4

可能是在MSSQL Server中添加特定位置的列的重复问题。 - JNK

这不是 SQL Server 中 sys.columns 表中的 column_id 字段吗？ - ganders

11个回答

14

解决方案：

对于没有依赖于正在更改的表格，导致级联事件触发的情况，此方法可行。 首先确保您可以删除要重构的表格而不会造成任何灾难性后果。记录与您的表格相关的所有依赖项和列约束（即触发器、索引等）。完成后，您可能需要将它们放回去。

步骤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：将临时表#tmp_myTable中的所有记录复制到新的（改进后的）表myTable中。

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

第6步：检查所有数据是否已经回到你的新的、改进后的表 myTable 中。如果是，请清理干净并删除临时表 #tmp_myTable 和 myTable_Copy 表（如果你选择重命名而不是删除它）。

- Milan

这是 SSDT 如何处理它的基本要点。1）使用新定义创建临时表。2）在临时表上创建聚集索引。 3）如果旧表中存在数据，则“INSERT INTO temp (columns...) SELECT (columns) FROM old”。4）删除旧表。5）对表和聚集索引进行 sp_rename。6）重新创建所有其他索引。 - Sunny Patel

4

如果您使用Management Studio中的GUI创建列，就可以实现这一点。我相信Management Studio实际上完全重新创建了表，这就是为什么会出现这种情况的原因。

正如其他人所提到的，表中列的顺序并不重要，如果有问题则说明代码存在问题。

- Abe Miessler

2

在SQL企业管理工作室中，打开您的表格，在您想要添加列的位置添加列，然后生成更改脚本--而不是保存更改。您可以在SQL中看到如何完成此操作。

简而言之，其他人所说的是正确的。 SQL管理工作室将所有数据提取到临时表中，删除该表，以正确顺序重新创建它，并将临时表数据放回其中。没有一个简单的语法来添加一个特定位置的列。

- user2124444

2

/* 用于更改表格列顺序的脚本
注意：这将创建一个新表格来替换原始表格。
警告：原始表格可能会被删除。
但是它不会复制触发器或其他表属性，只复制数据
*/

按照所需顺序生成一个新表格的列

Select Column2, Column1, Column3 Into NewTable from OldTable

删除原始表格

Drop Table OldTable;

重命名新表格

EXEC sp_rename 'NewTable', 'OldTable';

- Glyn

虽然这是一个不错的解决方案，但它会删除原始表。如果你不知道自己在做什么，请不要尝试。 - Fedeco

1

这是完全可能的。但是，除非您知道自己在处理什么，否则不应该这样做。我花了大约两天时间才想明白它。这是一个存储过程，我输入： - 数据库名称（模式名称为“_”以提高可读性） - 表名 - 列 - 列数据类型（添加的列始终为空，否则您将无法插入） - 新列的位置。

由于我正在使用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  ')

END

- Zlatin Todorinski

该过程可以改进的地方是，您可以为新列添加默认值，然后将旧列分成两部分：新列之前和之后。在插入值时，在插入语句的选择语句中添加“@beforeColumn + default + @after column”。如果有更多改进建议，请提出 :) - Zlatin Todorinski

1

如果您正在使用GUI进行此操作，则必须取消选择以下选项，以允许删除表格。

- Per Johansson

1

即使这个问题很旧，关于管理工作室的更精确的答案是必需的。

您可以手动创建列或使用管理工作室。但是，如果表中已经有太多数据，则需要重新创建表，并且会导致超时，请避免使用，除非该表很轻。

要更改列的顺序，您只需要在管理工作室中移动它们。这应该不需要（异常很可能存在）重新创建表格，因为它很可能会更改表格定义中列的排序。

我曾经在许多场合以这种方式处理无法使用GUI添加列的表格。然后使用管理工作室的GUI移动列，然后简单地保存它们。

您将从保证超时到等待几秒钟。

- Rv3

1

在Microsoft SQL Server Management Studio（MSSQL的管理工具）中，只需进入表格的“设计”模式，然后将列拖动到新位置即可。虽然不是命令行操作，但您可以这样做。

- paparazzo

-1

创建新的添加列表脚本，例如：[DBName].[dbo].[TableName]_NEW
将旧表数据复制到新表中：INSERT INTO newTable (col1,col2,...) SELECT col1,col2,... FROM oldTable
检查旧记录和新记录是否相同：
删除旧表
将新表重命名为旧表
重新运行您的存储过程以添加新的列值

-- 1. Create New Add new Column Table Script
CREATE TABLE newTable
(   [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. COPY old table data to new table:
INSERT INTO newTable ([new_column],[idx],[name],[active])
SELECT [new_column],[idx],[name],[active]
FROM oldTable
-- 3. Check records old and new are the same: 
select sum(cnt) FROM (
SELECT 'table_1' AS table_name, COUNT(*) cnt FROM newTable 
UNION
SELECT 'table_2' AS table_name, -COUNT(*)  cnt FROM oldTable
) AS cnt_sum 
-- 4. DROP old table
DROP TABLE oldTable
-- 5. rename newtable to oldtable
USE [DB_NAME]
EXEC sp_rename newTable, oldTable

- ping George

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Mike M. · Accepted Answer

45

很抱歉，您不能这样做。

如果您真的希望按照那个顺序进行排序，您需要创建一个新表，并按照该顺序添加列并复制数据。或者重命名列等。没有简单的方法。

- Mike M.

7

您只需在管理工具中拖动该列即可。这非常简单易行。 - paparazzo

6

如果你有一个非常大的表格，行数很多，那么管理工作室可以为你销毁和重建表格，但最好只进行修改。如果能在不销毁表格的情况下完成这个过程会更好。由于此过程可能太耗时，因此管理工作室实际上有一个选项来阻止你这样做。 - Peter

2

@PreguntonCojoneroCabrón管理工作室在维护数据时，会将其复制到临时位置，如果需要对表进行破坏性更改。将表中的列按特定顺序排列是这样的操作之一，其中需要删除并重新创建表。 SSMS将数据复制出来，删除表，使用新模式重新创建表，然后将数据添加回表中。 - squillman