Menu
Insert data into a table variable. We do not require dropping the table variable. As mentioned earlier, the scope of the table variable is within the batch. The scope of it lasts at the end of the batch or procedure.
A table variable isn't a . NET object, which holds unmanaged resources; in T-SQL there is nothing to dispose. If you would use a temp table, then you could drop it like every other object; but that's all.
Usage. The DROP VARIABLE statement eliminates a SQL variable that was previously created using the CREATE VARIABLE statement. Variables are automatically eliminated when the database connection is released.
The SQL DROP TABLE statement is used to remove a table definition and all the data, indexes, triggers, constraints and permission specifications for that table.
Table variables are automatically local and automatically dropped -- you don't have to worry about it.
if somebody else comes across this... and you really need to drop it like while in a loop, you can just delete all from the table variable:
DELETE FROM @tableVariableName
Table variables are just like int or varchar variables.
You don't need to drop them. They have the same scope rules as int or varchar variables
The scope of a variable is the range of Transact-SQL statements that can reference the variable. The scope of a variable lasts from the point it is declared until the end of the batch or stored procedure in which it is declared.
But you all forgot to mention, that if a variable table is used within a loop it will need emptying (delete @table) prior to loading with data again within a loop.
Just Like TempTables, a local table variable is also created in TempDB. The scope of table variable is the batch, stored procedure and statement block in which it is declared. They can be passed as parameters between procedures. They are automatically dropped when you close that session on which you create them.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With