如何在SQL Server中一次更改多个列

我需要ALTER一个表中几个列的数据类型。

对于单个列，下面的工作很好:

ALTER TABLE tblcommodityOHLC
ALTER COLUMN
CC_CommodityContractID NUMERIC(18,0)

但是如何在一条语句中修改多个列呢?以下选项无效:

ALTER TABLE tblcommodityOHLC
ALTER COLUMN
CC_CommodityContractID NUMERIC(18,0),
CM_CommodityID NUMERIC(18,0)
413339 次浏览
小开
这是不可能的。你需要一个一个地做。 你可以:< / p >
  1. 创建一个临时表，其中包含已修改的列
  2. 复制数据
  3. 删除原来的表(请仔细检查!)
  4. 将临时表重命名为原来的名称
小开

如果您在management studio中进行更改并生成脚本，它会生成一个新表，并将旧数据插入到具有更改的数据类型的表中。下面是一个更改两个列的数据类型的小示例

/*
12 August 201008:30:39
User:
Server: CLPPRGRTEL01\TELSQLEXPRESS
Database: Tracker_3
Application:
*/


/* 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
ALTER TABLE dbo.tblDiary
DROP CONSTRAINT FK_tblDiary_tblDiary_events
GO
ALTER TABLE dbo.tblDiary_events SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_tblDiary
(
Diary_ID int NOT NULL IDENTITY (1, 1),
Date date NOT NULL,
Diary_event_type_ID int NOT NULL,
Notes varchar(MAX) NULL,
Expected_call_volumes real NULL,
Expected_duration real NULL,
Skill_affected smallint NULL
)  ON T3_Data_2
TEXTIMAGE_ON T3_Data_2
GO
ALTER TABLE dbo.Tmp_tblDiary SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_tblDiary ON
GO
IF EXISTS(SELECT * FROM dbo.tblDiary)
EXEC('INSERT INTO dbo.Tmp_tblDiary (Diary_ID, Date, Diary_event_type_ID, Notes, Expected_call_volumes, Expected_duration, Skill_affected)
SELECT Diary_ID, Date, Diary_event_type_ID, CONVERT(varchar(MAX), Notes), Expected_call_volumes, Expected_duration, CONVERT(smallint, Skill_affected) FROM dbo.tblDiary WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_tblDiary OFF
GO
DROP TABLE dbo.tblDiary
GO
EXECUTE sp_rename N'dbo.Tmp_tblDiary', N'tblDiary', 'OBJECT'
GO
ALTER TABLE dbo.tblDiary ADD CONSTRAINT
PK_tblDiary PRIMARY KEY NONCLUSTERED
(
Diary_ID
) WITH( PAD_INDEX = OFF, FILLFACTOR = 86, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON T3_Data_2


GO
CREATE UNIQUE CLUSTERED INDEX tblDiary_ID ON dbo.tblDiary
(
Diary_ID
) WITH( PAD_INDEX = OFF, FILLFACTOR = 86, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON T3_Data_2
GO
CREATE NONCLUSTERED INDEX tblDiary_date ON dbo.tblDiary
(
Date
) WITH( PAD_INDEX = OFF, FILLFACTOR = 86, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON T3_Data_2
GO
ALTER TABLE dbo.tblDiary WITH NOCHECK ADD CONSTRAINT
FK_tblDiary_tblDiary_events FOREIGN KEY
(
Diary_event_type_ID
) REFERENCES dbo.tblDiary_events
(
Diary_event_ID
) ON UPDATE  CASCADE
ON DELETE  CASCADE


GO
COMMIT
小开

在一个ALTER TABLE语句中执行多个ALTER COLUMN操作是不可能的。

参见ALTER TABLE语法在这里

你可以做多个ADD或多个DROP COLUMN，但只有一个ALTER 列> < /代码。< / p >
小开 
select 'ALTER TABLE ' + OBJECT_NAME(o.object_id) +
' ALTER COLUMN ' + c.name + ' DATETIME2 ' +
CASE WHEN c.is_nullable = 0 THEN 'NOT NULL' ELSE 'NULL' END
from sys.objects o
inner join sys.columns c on o.object_id = c.object_id
inner join sys.types t on c.system_type_id = t.system_type_id
where o.type='U'
and c.name = 'Timestamp'
and t.name = 'datetime'
order by OBJECT_NAME(o.object_id)

由devio提供

小开

将ALTER COLUMN语句放在括号内，它应该可以工作。

ALTER TABLE tblcommodityOHLC alter ( column
CC_CommodityContractID NUMERIC(18,0),
CM_CommodityID NUMERIC(18,0) )
小开

下面的解决方案不是一个单独的语句来修改多个列，但是，是的，它使生活变得简单:

  1. 生成一个表的CREATE脚本。

  2. 第一行用ALTER TABLE [TableName] ALTER COLUMN替换CREATE TABLE

  3. 从列表中删除不需要的列。

  4. 根据需要更改列数据类型。

  5. 执行寻找和替换…如下:

    1. 发现:NULL,
    2. 替换为:NULL; ALTER TABLE [TableName] ALTER COLUMN
    3. 点击取代按钮。
      4. 李< / ol > < / >

    4. 运行脚本。

    希望能节省很多时间:)

小开 
-- create temp table
CREATE TABLE temp_table_alter
(
column_name varchar(255)
);


-- insert those coulmns in temp table for which we nee to alter size of columns
INSERT INTO temp_table_alter (column_name) VALUES ('colm1');
INSERT INTO temp_table_alter (column_name) VALUES ('colm2');
INSERT INTO temp_table_alter (column_name) VALUES ('colm3');
INSERT INTO temp_table_alter (column_name) VALUES ('colm4');


DECLARE @col_name_var varchar(255);
DECLARE alter_table_cursor CURSOR FOR
select column_name from temp_table_alter ;


OPEN alter_table_cursor
FETCH NEXT FROM alter_table_cursor INTO @col_name_var
WHILE @@FETCH_STATUS = 0
BEGIN


PRINT('ALTER COLUMN ' + @col_name_var);
EXEC ('ALTER TABLE Original-table  ALTER COLUMN ['+ @col_name_var + '] DECIMAL(11,2);')


FETCH NEXT FROM alter_table_cursor INTO @col_name_var
END


CLOSE alter_table_cursor
DEALLOCATE alter_table_cursor


-- at the end drop temp table
drop table temp_table_alter;
小开
正如其他人回答的那样，你需要多个ALTER TABLE语句 试一试:< / p > 
ALTER TABLE tblcommodityOHLC alter column CC_CommodityContractID NUMERIC(18,0);
ALTER TABLE tblcommodityOHLC alter column CM_CommodityID NUMERIC(18,0);
小开

我们可以像这样在一个查询中修改多个列:

ALTER TABLE `tblcommodityOHLC`
CHANGE COLUMN `updated_on` `updated_on` DATETIME NULL DEFAULT NULL AFTER `updated_by`,
CHANGE COLUMN `delivery_datetime` `delivery_datetime` DATETIME NULL DEFAULT CURRENT_TIMESTAMP AFTER `delivery_status`;

只需以逗号分隔查询。

小开

如果我正确理解了你的问题，你可以使用下面提到的查询在一个表中添加多个列。

查询:

Alter table tablename add (column1 dataype, column2 datatype);
小开

正如很多人所说，你需要使用多个ALTER COLUMN语句，每一列对应一个你想修改的列。

如果希望将表中的全部或部分列修改为相同的数据类型(例如将VARCHAR字段从50个字符扩展到100个字符)，可以使用下面的查询自动生成所有语句。如果您想在多个字段中替换相同的字符(例如从所有列中删除\t)，此技术也很有用。

SELECT
TABLE_CATALOG
,TABLE_SCHEMA
,TABLE_NAME
,COLUMN_NAME
,'ALTER TABLE ['+TABLE_SCHEMA+'].['+TABLE_NAME+'] ALTER COLUMN ['+COLUMN_NAME+'] VARCHAR(300)' as 'code'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'your_table' AND TABLE_SCHEMA = 'your_schema'

这将为你的每一列生成ALTER TABLE语句。

小开

多亏了Evan的代码示例，我能够对其进行更多的修改，使其更特定于以特定列名开头的表，并处理约束的细节。我运行该代码，然后复制[code]列并毫无问题地执行它。

USE [Table_Name]
GO
SELECT
TABLE_CATALOG
,TABLE_SCHEMA
,TABLE_NAME
,COLUMN_NAME
,DATA_TYPE
,'ALTER TABLE ['+TABLE_SCHEMA+'].['+TABLE_NAME+'] DROP CONSTRAINT [DEFAULT_'+TABLE_NAME+'_'+COLUMN_NAME+'];
ALTER TABLE  ['+TABLE_SCHEMA+'].['+TABLE_NAME+'] ALTER COLUMN ['+COLUMN_NAME+'] datetime2 (7) NOT NULL
ALTER TABLE  ['+TABLE_SCHEMA+'].['+TABLE_NAME+'] ADD CONSTRAINT [DEFAULT_'+TABLE_NAME+'_'+COLUMN_NAME+'] DEFAULT (''3/6/2018 6:47:23 PM'') FOR ['+COLUMN_NAME+'];
GO' AS '[CODE]'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE 'form_%' AND TABLE_SCHEMA = 'dbo'
AND (COLUMN_NAME = 'FormInserted' OR COLUMN_NAME = 'FormUpdated')
AND DATA_TYPE = 'datetime'
小开

如果你不想自己写整个东西，把所有的列都改成相同的数据类型，这可以让它更容易:

select 'alter table tblcommodityOHLC alter column '+name+ 'NUMERIC(18,0);'
from syscolumns where id = object_id('tblcommodityOHLC ')

您可以复制和粘贴输出作为您的查询


提交答案