Reputation: 2657
Using MS SQL Server I have the below SQL that updates a table that begins with 'tbl_Test%'
.
However, I have the situation where I can have multiple tables beginning with the name 'tbl_Test%'
. How would I modify the code so it will loop through and update all the tbl_Test%
tables? At the moment it only updates the first one.
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
Upvotes: 0
Views: 84
Reputation: 2657
I got it working using the below:
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
Upvotes: 1
Reputation: 1484
I think you need somethin like that :
DECLARE @NSQL NVARCHAR(MAX)=''
SELECT
@NSQL = @NSQL+CHAR(13)+'UPDATE '+INFORMATION_SCHEMA.TABLES.TABLE_NAME+' SET [WEEKSTART] = CONVERT(NVARCHAR,convert(datetime, LEFT(WEEKSTART,10), 104),112);'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'tbl_Test________________%'
AND TABLE_TYPE = 'BASE TABLE'
PRINT(@NSQL)
EXEC SP_EXECUTESQL @NSQL
Upvotes: 0