Sql Server : How to TRUNCATE table variable
- Posted by Sqltimes
- On May 2, 2015
- 0 Comments
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.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
---- Throw error--DECLARE @Table_Variable TABLE (ID2 INT)INSERT INTO @Table_Variable (ID2) VALUES(1), (2)SELECT * FROM @Table_VariableTRUNCATE TABLE @Table_Variable -- This code throws errorGO---- Error message--Msg 102, Level 15, State 1, Line 5Incorrect syntax near '@Table_Variable'. |
We get the same error message when we use TRUNCATE TABLE or DROP TABLE with table variable.
Solution:
|
1
2
3
4
5
6
7
8
9
|
---- Correct way to empty table variable--DECLARE @Table_Variable TABLE (ID2 INT)INSERT INTO @Table_Variable (ID2) VALUES(1), (2)SELECT * FROM @Table_VariableDELETE @Table_VariableGO |
After DELETE, only the contents are removed; The variable and its structure are still available for INSERTing again, within the scope.
Hope this helps,
_Sqltimes
0 Comments