sql - T SQL 循环遍历表和更新字段

Question

使用 MS SQL Server 我有下面的 SQL 更新以 . 开头的表'tbl_Test%'。

但是，我有多个表以 name 开头的情况'tbl_Test%'。我将如何修改代码，使其循环并更新所有tbl_Test%表？目前它只更新第一个。

DECLARE @NSQL NVARCHAR(MAX)
DECLARE @SOURCETABLE NVARCHAR(MAX)

SELECT @SOURCETABLE = TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'tbl_Test%' AND TABLE_TYPE = 'BASE TABLE'

SET @NSQL = 'UPDATE [' + @SOURCETABLE + '] SET [WEEKSTART] = CONVERT(NVARCHAR,convert(datetime, LEFT(WEEKSTART,10), 104),112)'

--PRINT @NSQL

EXEC SP_EXECUTESQL @NSQL

标签： sqlsql-server

Answer 1

我使用以下方法使其工作：

DECLARE @NSQL NVARCHAR(MAX)
DECLARE @SOURCETABLE NVARCHAR(MAX) 

DECLARE test_cursor CURSOR FOR 

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'tbl_Test________________%' AND TABLE_TYPE = 'BASE TABLE'

OPEN test_cursor  
FETCH NEXT FROM test_cursor INTO @SOURCETABLE

WHILE @@FETCH_STATUS = 0  
BEGIN  

SET @NSQL = 'UPDATE [' + @SOURCETABLE + '] SET [WEEKSTART] = CONVERT(NVARCHAR,convert(datetime, LEFT(WEEKSTART,10), 104),112)'

--PRINT @NSQL

EXEC SP_EXECUTESQL @NSQL

FETCH NEXT FROM test_cursor INTO @SOURCETABLE


END 

CLOSE test_cursor  
DEALLOCATE test_cursor