Quick one today:
Table variables are ubiquitous. There are several benefits to using them in your T-SQL code. There are some nuances between table variable and temporary table that we discussed previously; Today, we’ll look at dropping table variable.
How do we DROP a table variable? A table variable is not fully a table, so we cannot use DROP statement or TRUNCATE statement.
-- -- Throw error -- DECLARE @Table_Variable TABLE (ID2 INT) INSERT INTO @Table_Variable (ID2) VALUES(1), (2) SELECT * FROM @Table_Variable TRUNCATE TABLE @Table_Variable -- This code throws error GO -- -- Error message -- Msg 102, Level 15, State 1, Line 5 Incorrect syntax near '@Table_Variable'.
We get the same error message when we use TRUNCATE TABLE or DROP TABLE with table variable.
-- -- Correct way to empty table variable -- DECLARE @Table_Variable TABLE (ID2 INT) INSERT INTO @Table_Variable (ID2) VALUES(1), (2) SELECT * FROM @Table_Variable DELETE @Table_Variable GO
After DELETE, only the contents are removed; The variable and its structure are still available for INSERTing again, within the scope.
Hope this helps,