Deleting Records And Table Variables

In this post I talked about the creation of a table variable and then inserting records into our table variable. At times we will need to remove records from our table variable, and the way to do this is just like removing records from normal tables using the "DELTE" statement:

1. USE master;
2. GO
3. -- The creation of our Table Variable.
4. DECLARE @UserAccounts TABLE
5. (
6.     firstName VARCHAR(50)
7.     , lastName VARCHAR(50)
8. );
10. -- Inserting 3 records into our Table Variable.
11. INSERT INTO @UserAccounts
12. VALUES ('John', 'Doe');
14. INSERT INTO @UserAccounts
15. VALUES ('Jane', 'Doe');
17. INSERT INTO @UserAccounts
18. VALUES ('Jim', 'Doe');
20. -- Delete a record from our Table Variable.
21. DELETE FROM @UserAccounts
22. WHERE firstName = 'John';
24. -- Selecting the records from our Table Variable.
25. SELECT * FROM @UserAccounts;

The output of this query should be:



(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
firstName                                          lastName
-------------------------------------------------- --------------------------------------------------
Jane                                               Doe
Jim                                                Doe

(2 row(s) affected)