T SQL Loop through tables and update field

Question

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

Look into CURSOR commands to loop through all the tables and execute on each. — Ed Callahan, Dec 12 '18 at 13:50
I don't think you necessarily need a loop. See one of the various questions on SO about selecting a comma-delimited string (like [this one](https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv)), use a semicolon instead of a comma as your delimiter, and you can build a whole series of statements directly into `@NSQL`. — Joe Farrell, Dec 12 '18 at 13:55
Also note that your `LIKE` expression will match tables that begin with, for instance, `tblxTest` too. To match a literal underscore, use an operand starting with `tbl[_]Test` instead. Finally, it's a good idea to specify the schema (`TABLE_SCHEMA` from `INFORMATION_SCHEMA.TABLES`) in each of your `UPDATE` statements. — Joe Farrell, Dec 12 '18 at 13:58
What are you attempting to do? Why are you using string operations on date/time values? Is dynamic SQL really necessary? Why do you have multiple tables with the same columns? — Gordon Linoff, Dec 12 '18 at 14:05
@GordonLinoff because its an ETL load and I'm loading them dynamically — Michael, Dec 12 '18 at 14:07

score 1 · Answer 1 · answered Dec 12 '18 at 14:11

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

Zeki Gumus · Answer 2 · 2018-12-12T14:04:38.663

0

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

edited Dec 12 '18 at 14:04

answered Dec 12 '18 at 13:54

Zeki Gumus

1,484
7
14

You should add a semicolon at the end of the line. It will probably work like this but much cleaner to end your statements. – Sean Lange Dec 12 '18 at 13:59
@SeanLange You are absolutely right. I have missed it.Thanks for double check. – Zeki Gumus Dec 12 '18 at 14:05

T SQL Loop through tables and update field

2 Answers2