Drop
DROP TABLE lets us Delete a table from a data base not just its data like T-SQL TRUNCATE or T-SQL DELETE. Can add IF EXISTS starting from SQL Server 2016. cannot drop a table that is referenced by a foreign key constraint.
drop TABLE My_Table
-- OR
drop TABLE IF EXISTS My_Table
Similar to Temp Tables dropping where IF NOT NULL is similar to IF EXISTS:
From Temp Tables
Go to text ā
Temporary Tables
Temp Tables are result sets that are stored in memory for the live of the connection session. There are two types:
Local
- Only available for the session that created them.
- Deleted once the session is terminated.
- Denoted with a
#prepended to the table name.
Global
- Available for all sessions and users.
- Not deleted until the last session using them is terminated
- CAN be explicitly deleted
- Denoted with
##prepended to the table name
Benefits
- Stored in memory and are FAST
- Helps to modularize your code instead of monolithic queries
Usage
You CAN simply just create the new table but to use the tables iteratively while testing they need to be explicitly deleted for re-use. The best method for this is the following code:
IF OBJECT_ID('Tempdb.dbo.#table') IS NOT NULL DROP TABLE #table
SELECT *
INTO #table
FROM other_table
WHERE Age > 55
Template Version
IF OBJECT_ID('Tempdb.dbo.#<Temp Table Name, Table,>') IS NOT NULL DROP TABLE #<Temp Table Name, Table,>
Backlinks