CODEWITHSUNDEEP
sqlsql-server
Michael
Michael

Reputation: 2657

T SQL Loop through tables and update field

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

Answers (2)

Michael
Michael

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

Zeki Gumus
Zeki Gumus

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

Related Questions