T-SQL循环遍历表格

Question

我正在尝试对一组使用where子句进行选择的表执行相同的操作序列。

Select table_name 
From INFORMATION_SCHEMA.COLUMNS 
Where column_name = 'fieldA';

这会生成一个表名列表，这些是我想用 for 循环处理的表。有没有一种方法可以遍历这个表列表，就像这样？

for i in @tablelist:

    alter table i add new_col varchar(8);
    update i set new_col = old_col;

- joshi123

---

嗯...不知道你是否可以在DML查询中运行DDL...事实上，我非常确定你不能这样做。 - Jeremy Holovacs

你必须使用动态SQL。 - Bartosz Siemasz

为什么要添加一个新列，并将值设置为另一列的相同值？这似乎有点愚蠢，因为两列中的数据相同。也许您接下来会删除旧列？也许只需将这些列重命名可能是一个选项。 - Sean Lange

我正在对新列进行其他操作，删除字符以更改格式。我认为这与问题的范围无关。 - joshi123

明白了。如果你正在做其他有意义的事情，那很好。根据发布的内容，似乎简单地重命名可能是更好的选择。看起来事情并不是那么简单。 :) - Sean Lange

Answer 1

您可以尝试以下操作：

DECLARE @Table TABLE
(
TableName VARCHAR(50),
Id int identity(1,1)
)


INSERT INTO @Table
Select table_name From INFORMATION_SCHEMA.COLUMNS 
Where column_name = 'fieldA'

DECLARE @max int
DECLARE @SQL VARCHAR(MAX) 
DECLARE @TableName VARCHAR(50)
DECLARE @id int = 1

select @max = MAX(Id) from @Table


WHILE (@id <= @max)
BEGIN

SELECT @TableName = TableName FROM @Table WHERE Id = @id
SET @SQL =     'alter table '+ @TableName +' add new_col varchar(8);
                update '+ @TableName + ' set new_col = old_col;'
PRINT(@SQL)  --COMMENT THIS LINE OUT AND COMMENT IN THE NEXT EXEC(@SQL) IF YOU SEE THE CORRECT OUTPUT
--EXEC(@SQL)
SET @id = @id +1
END

- LONG

Answer 2

您不需要使用循环来完成此操作。您可以使用系统视图为您生成动态SQL，这比使用循环更简单（而且更快）。

declare @SQL nvarchar(max) = ''

select @SQL = @SQL + 'alter table ['+ c.TABLE_NAME +'] add new_col varchar(8);
                update ['+ c.TABLE_NAME + '] set new_col = old_col;'
from INFORMATION_SCHEMA.COLUMNS c
where c.COLUMN_NAME = 'fieldA'

select @SQL
--uncomment below when you are satisfied with the dynamic sql
--exec sp_executesql @SQL

- Sean Lange

---

在最后一步中，写入EXEC(@SQL)。 - Bartosz Siemasz

@BartoszSiemasz 不行。使用sp_executesql是更好的选择。在这种情况下，这并不重要，但EXEC不允许参数，而sp_executesql则可以。https://sqlblog.org/2011/09/17/bad-habits-to-kick-using-exec-instead-of-sp_executesql - Sean Lange

我遇到了错误 The data types nvarchar(max) and varchar are incompatible in the subtract operator. 在每个表中，old_col 和 fieldA 字段始终为 varchar(10)。尝试更改 declare @SQL varchar(max) = '' 但是仍然出现相同的错误。 - joshi123

不需要道歉。我只是在寻找一条更简单的路径来到达终点。 :) 很高兴这对你有用。 - Sean Lange

感谢@joshi123。当然我无法测试这个。显然你需要将其更改为TABLE_NAME。我会更新我的答案。 - Sean Lange

Answer 3

循环时，您可以使用cursors或while exists结构，其中每次处理条目时都会从临时表中删除条目。

至于将表名传递给查询，没有办法在不创建动态SQL查询的情况下执行此操作。通过在字符串中连接表名来创建查询，将其放入正确的位置。如果存在任何空格或奇怪字符，请使用 QUOTENAME 函数执行名称引用。

游标示例：

DECLARE @tables TABLE (
    Name SYSNAME
);
DECLARE @tableName SYSNAME;
DECLARE @sql NVARCHAR(4000);

INSERT INTO @tables
SELECT i.table_name
FROM INFORMATION_SCHEMA.COLUMNS i
WHERE i.column_name = 'fieldA';

DECLARE cur CURSOR LOCAL FAST_FORWARD
FOR
SELECT Name
FROM @tables;

OPEN cur;

FETCH NEXT FROM cur
INTO @tableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = N'ALTER TABLE ' + QUOTENAME(@tableName) + N' ADD new_col varchar(8);';
    SET @sql = @sql + N'UPDATE ' + QUOTENAME(@tableName) + N' SET new_col = old_col;';

    EXEC sp_executesql @sql;

    FETCH NEXT FROM cur
    INTO @tableName;    
END

CLOSE cur;
DEALLOCATE cur;

- nejcs

Answer 4

一种完成此操作的方法是将您想要更改的表列表馈送到 SQL 光标中，并通过光标迭代执行每个表上的 ALTER 表命令。关于如何创建 SQL 光标的文档可以在此处找到：https://learn.microsoft.com/en-us/sql/t-sql/language-elements/declare-cursor-transact-sql 另一种完成此操作的方法是在 SQL 中使用带有计数迭代器的 while 循环。有关此解决方案的信息可以在此处找到：https://learn.microsoft.com/en-us/sql/t-sql/language-elements/while-transact-sql

- DBAMan