Updating table with table variable
This means that the SET clause of the UPDATE statement is evaluated once for every row in the updated table.
With the SELECT statement, you can’t assign variables and return an output in the same statement.
For instance, consider the following example: DECLARE @tbl TABLE ( ordinal int NOT NULL, word varchar(100) NOT NULL, PRIMARY KEY CLUSTERED (ordinal) ); INSERT INTO @tbl (ordinal, word) VALUES (1, 'This'), (2, 'is'), (3, 'a'), (4, 'test'), (5, 'of'), (6, 'string'), (7, 'concatenation'); DECLARE @output varchar(1000)=''; On a side-note: We looked at a more advanced string concatenation example, using XML, in a previous post.
Just like the SELECT statement, the UPDATE statement executes row-by-row on the table being updated.
are just one solution; other ways for handling an independent physical or logical materialization of a set include table variables and table expressions such as views, inline user-defined functions (UDFs), derived tables, and common table expressions (CTEs).