How to use while loop to iterate through each table name in SQL Server?

Question

I'm working with Dynamic SQL (still in the learning phase) and I'm stuck at a part where I need to use a WHILE loop:

  SET @tableName = (SELECT DISTINCT TableName FROM @dataStructure)

Here basically I want to make sure that the operations inside the while loop should occur for all the tables in the @tableName (defined above). I don't know how I can give this condition as an input for the while loop.

   WHILE()  #HOW CAN I PUT THE CONDITION HERE????
   BEGIN
       SET @str = ''
       SET @sqlstr = ''
    
       SELECT @table = TableName FROM @dataStructure
    
       SET @str = 'UPDATE a0' + char(13) + char(10)
                + ' SET a0.Mjolnir_Source_ID = CONCAT( ' 
    
    SELECT @str = @str + IIF(ReferenceTable  IS NULL, 'a0.' + columnName , alias + '.Mjolnir_Source_ID') + ',' 
                        FROM @dataStructure
                        WHERE TableName = @tableName AND ReferenceTable IS NOT NULL
                        ORDER BY columnName
    
    SELECT @str = @str + ') FROM ' + @table + ' a0'
    
    SELECT @sqlstr = @sqlstr +  +
                        + ' INNER JOIN ' + QUOTENAME(@U4SM_db_name) + '.dbo.' + QUOTENAME(ReferenceTable) + ' '  + alias + char(13) + char(10)
                        + ' ON a0.' + columnName + ' = ' + alias + '.' + ReferenceColumn + char(13) + char(10)
                        FROM @dataStructure 
                        WHERE TableName = @tableName AND ReferenceTable IS NOT NULL
                        ORDER BY columnPosition
    
    
    select @str + @sqlstr
    select @sqlstr
    SET @tableName = @tableName + 1
 END

Can anyone please help me out here?

Personally, I wouldn't use a WHILE and would use string aggregation. What are you trying to achieve here against each table? — Thom A
– Thom A ♦, Commented Sep 29, 2020 at 16:15
@Larnu I have list of tables inside the variable "@tableName" and i strictly want to do this following operation only for those tables. — astroluv
– astroluv, Commented Sep 29, 2020 at 16:19
That doesn't answer the question I asked. What is it that this task that you're trying to do is doing? — Thom A
– Thom A ♦, Commented Sep 29, 2020 at 16:48

seanb · Accepted Answer · 2020-09-29 16:46:37Z

1

Here's an example of a WHILE loop. Basically, you get the first TableName, then if it's NOT NULL, you do your functions. Then get the next table name, and repeat as necessary.

DECLARE @CurrentTableName nvarchar(100)
DECLARE @CustomSQL nvarchar(4000)

SET @CurrentTableName = (SELECT TOP 1 TableName FROM @dataStructure ORDER BY TableName)

WHILE @CurrentTableName IS NOT NULL
BEGIN
    SET @CustomSQL = 'SELECT TOP 10 * FROM ' + @CurrentTableName
    EXEC (@CustomSQL)
    SET @CurrentTableName = (SELECT TOP 1 TableName FROM @dataStructure WHERE TableName > @CurrentTableName ORDER BY TableName)
END

Note that SQL commands often cannot contain variable names in key spots (e.g., SELECT * FROM @tableName). Instead, you save it as an SQL string (what I've called @CustomSQL above) and then EXEC it (put brackets around the variable name though).

Edit: Do this on a test site first before production, and know where the 'cancel query' button is. It's not often, but it's also not unknown, that the 'getting the next row' part isn't properly written and it just runs in a perpetual loop.

edited Sep 29, 2020 at 16:46

answered Sep 29, 2020 at 16:40

seanb

6,6952 gold badges7 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

astroluv Over a year ago

Yes I tried to implement it. But one issue that i'm getting is that, As i want to update every table in the "@CurrentTableName" variable but Its always taking the last record from it. I believe it has to do with what you mentioned? like "getting the next row". Any idea how i can solve that?

seanb Over a year ago

Ensure the 'order by' is correct when selecting the 'top 1' values. Note also that the statement within the WHILE loop also has a WHERE clause to get the next value (WHERE TableName > @CurrentTableName). Ensure this goes the right way, and is not a >= or <=. Another alternative involves deleting the current record fromt the table, instead of just selecting the next one. Then you can just get any of the remaining. Here's a DB<>fiddle with both approaches note I changed the EXEC to a PRINT for the fiddle.

seanb Over a year ago

PS this is one of the reasons imo why cursors are often better. Usually when writing WHERE loops, I write the looping logic first without any of the processing logic - just printing out the current values of any variables set (e.g., in this case, @CurrentTableName)

astroluv Over a year ago

I didn't want to use the second approach as it will delete the records from the table and i didn't want to do that. But i used your first approach and after a minor correction it worked. Thanks.

Derviş Kayımbaşıoğlu · Accepted Answer · 2020-09-29 16:23:01Z

0

FETCH CURSOR with WHILE. Example:

DECLARE myCursor CURSOR FOR  
    SELECT DISTINCT TableName FROM @dataStructure;
OPEN myCursor;  
FETCH NEXT FROM myCursor INTO @table:Name;  
WHILE @@FETCH_STATUS = 0  
   BEGIN  
      Print '   ' + @TableName 
      FETCH NEXT FROM myCursor INTO @TableName;  
   END;  
CLOSE myCursor;  
DEALLOCATE myCursor;  
GO

answered Sep 29, 2020 at 16:23

Derviş Kayımbaşıoğlu

30.9k4 gold badges55 silver badges79 bronze badges

2 Comments

astroluv Over a year ago

Thats the thing. I want to use While loop instead of cursor.

SMor Over a year ago

@astroluv But your while loop is just a more error-prone and slower (usually) version of a cursor. Ignoring a set-based approach, a cursor is better for you generally. Dynamic sql requires an advanced knowledge of tsql - and you just aren't there yet. Simplify where you can.

Nick Fotopoulos · Accepted Answer · 2020-09-29 19:20:01Z

0

Don't recreate the wheel unless you need a better wheel:

sp_MSforeachtable

https://www.sqlshack.com/an-introduction-to-sp_msforeachtable-run-commands-iteratively-through-all-tables-in-a-database/

If you are worried about using an undocumented procedure in production that might change in the future, simply script it out and create your own custom named version.

answered Sep 29, 2020 at 19:20

Nick Fotopoulos

5895 silver badges15 bronze badges

3 Comments

seanb Over a year ago

I was going to post this too - but a) it's not clear the OP wants to update every table, or even that it can be done to every table, and b) they explicitly want to do a WHILE loop.

Nick Fotopoulos Over a year ago

You don't have to update every table. That's what the @whereand parameter if for: "By default, sp_MSforeachtable is applied to all user tables in the database. Use this parameter to filter the tables that you want to work with. On the next section, I will explain how you can filter the tables". OP could easily pass in the table names from the datasource they have.

Nick Fotopoulos Over a year ago

I didn't either until I looked it up while trying to answer this question. xD You can also use the ? placeholder in your query to filter tables. The query string you pass into @command will replace every occurrence of ? with the name of the table the query is being run against. So you could write a query like DELETE FROM ? WHERE '?' IN ('TableIWantToDelete', 'OtherTableIWantToDelete', 'TableIReallyHate')

Collectives™ on Stack Overflow

How to use while loop to iterate through each table name in SQL Server?

3 Answers 3

4 Comments

2 Comments

sp_MSforeachtable

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

sp_MSforeachtable

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related