How to make and use temporary tables in SQl server
Temporary table provides the great help to developer. These tables can be created at runtime according to need and can do the all kinds of operations that one normal table can do. But, based on the table types, the scope is limited. These tables are created inside Tempdb database. There are two type of Temp tables
1) Local Temp Table
2) Global Temp Table
Local Temp Table:- Local temp tables are only available to the current connection for the user and they are automatically deleted when the user disconnects from instances. But it is always better to use Drop table #tempname. Local temporary table name is stared with hash "#" .
Global Temp Table: Global Temporary tables name starts with a double hash "##" . Once this table has been created by a connection, like a permanent table it is then available to any user for that database connection. It can only be deleted once all connections have been closed. So we should use the global tables very rarely, they use the server memory more.
Example of Temporary table:-
Create PROCEDURE [dbo].[Event_GetSchedule]
(
@Date datetime
)
AS
CREATE TABLE #tempEvent
(
id int,
EventDate datetime,
StartTime varchar(10),
EndTime varchar(10),
EndDate datetime,
FacilityID int,
Customer varchar(100),
FirstName varchar(100),
LastName varchar(100),
Name varchar(100),
eventType char(1)
)
insert into #tempEvent SELECT
e.ID, e.EventDate, e.StartTime, e.EndTime, e.EndDate, e.FacilityID, e.Customer,
e.FirstName, e.LastName, e.FirstName + ' ' + e.LastName as Name,'A'
FROM
[Event] as e
INNER JOIN EventSchedule as esc ON esc.EventID = e.ID
WHERE DateDiff(DAY, esc.StartTime, @Date) = 0
select * from #tempEvent order by ID desc
drop table #tempEvent
Related Alrticles