I want to discuss about SQL Temporary Table.
Temporary tables are created in tempdb. They act like regular tables in that you can query their data via SELECT queries and modify their data via UPDATE, INSERT, and DELETE statements.
The scope of any particular temporary table is the session in which it is created; meaning it is only visible to the current user. Multiple users could create a temp table named #TableX and any queries run simultaneously would not affect one another.
Syntax :
|
1 2 3 4 5 6 7 8 |
CREATE TABLE #Cars ( Car_id int NOT NULL, ColorCode varchar(10), ModelName varchar(20), Code int, DateEntered datetime ) |
|
1 2 3 4 |
INSERT INTO #Cars (Car_id, ColorCode, ModelName, Code, DateEntered) VALUES (1,'BlueGreen', 'Austen', 200801, GETDATE()) SELECT Car_id, ColorCode, ModelName, Code, DateEntered FROM #Cars |
And Drop Syntex:
|
1 |
IF OBJECT_ID('tempdb..#ApprovalWaiting', 'U') IS NOT NULL DROP TABLE #ApprovalWaiting |
Table Variables:
|
1 2 3 4 5 6 7 8 |
DECLARE @Cars TABLE ( Car_id int NOT NULL, ColorCode varchar(10), ModelName varchar(20), Code int , DateEntered datetime ) |
|
1 2 3 4 |
INSERT INTO @Cars (Car_id, ColorCode, ModelName, Code, DateEntered) VALUES (1,'BlueGreen', 'Austen', 200801, GETDATE()) SELECT Car_id, ColorCode, ModelName, Code, DateEntered FROM @Cars |
@table_variables can only be accessed within the batch and scope in which they are declared. #temp_tables are accessible within child batches (nested triggers, procedure, exec calls). #temp_tables created at the outer scope (@@NESTLEVEL=0) can span batches too as they persist until the session ends. Neither type of object can be created in a child batch and accessed in the calling scope however as discussed next (global ##temp tables can be though).
Wonderful! thanks for sharing.
Excellent. I’m glad to find so many useful info right.
Highly energetic post. Thanks for sharing.