CODEWITHSUNDEEP
sqlsql-servert-sql
Tomb_Raider_Legend
Tomb_Raider_Legend

Reputation: 461

SQL Server - What is the easiest way to loop through the values of a table variable?

For example the table variable @TABEL_VARIABLE below including the values selected from ANOTHER_TABLE.myVar2 :

DECLARE @Table_Variable (myVar VARCHAR(MAX)) 

INSERT INTO @Table_Variable (myVar)
    SELECT myVar2 
    FROM ANOTHER_TABLE

What is the easiest way to loop through the values in @Table_Variable.myVar and put them inside another table or table variable?

Upvotes: 0

Views: 108

Answers (1)

SQLServerSteve
SQLServerSteve

Reputation: 342

Let me give a practical example I've used repeatedly to defeat the limitation against nested INSERT EXECs. Basically, this approach just increments a counter to loop over a table variable, much like in a standard For I = 0 loop in C# or VB.Net. In this particular example, I use it to select a different schema, table and column on each pass and retrieve an aggregate, which is then used to UPDATE values in another table. You could use the same approach to perform other operations in the loop besides doing a RBAR update to defeat INSERT EXEC; this is the most practical use I've found for it to date though. I'm sure there are ways the code can be improved (I also have more sophisticated versions to handle multiple conditions, etc.) but the design pattern remains the same. It probably won't perform well on large queries where set-based solutions are almost always preferable, but there are niche uses for this approach. I hope this helps; if you need any clarifications etc. please let me know. :)

DECLARE @SQLString nvarchar(max),
@CurrentTableVarID bigint = 0,
@MaxTableVarID bigint = 0,
@CounterCheck bigint = 0,
@ParameterDefinition nvarchar(500),
@MaxID bigint,
@MaxIDOut bigint

SET @SQLString = ''

SELECT @MaxTableVarID = Max(ID) FROM @ResultTable GROUP BY ID ORDER BY ID ASC
SELECT @CurrentTableVarID =Max(ID) FROM @ResultTable GROUP BY ID ORDER BY ID  DESC

WHILE @CurrentTableVarID <= @MaxTableVarID
BEGIN   

        SELECT @SchemaName = SchemaNAme, @TableName = TableName, 
        @ColumnName = ColumnName
        FROM @MyTableVar
        WHERE ID = @CurrentTableVarID

        SET @ParameterDefinition = '@MaxIDOut bigint OUTPUT';
        SET @SQLString = 'SELECT @MaxIDOut = Max(' + @ColumnName + ') FROM [' + @SchemaName + '].[' + @TableName + '] GROUP BY ' + @ColumnName + ' ORDER BY ' + @ColumnName + ' ASC'

        EXEC sp_executesql @SQLString, @ParameterDefinition, @MaxIDOut = @MaxID  OUTPUT

        UPDATE @ResultTable
        SET MaxID = @MaxID
        WHERE ID = @CurrentTableVarID


    SET     @CounterCheck = @CounterCheck  + 1
    SET @CurrentTableVarID = @CurrentTableVarID + 1 -- increment the loop
END

Upvotes: 1

Related Questions