0Temp Table in SQL Server
The temp table in SQL Server can be created at the run-time, and perform all the operation that a normal table can do. There are two types of Temporary Tables in SQL Server: Local Temporary Tables, and Global Temporary Tables. In this article we will show you, How to work with both Local, and Global Temp table in SQL.
Before we get into the examples, following list will show you, where we can use the SQL temp tables:
- When we are working with the complex joins.
- Temp tables are useful to replace the costly cursors. We can use this temp table to store the result set data, and then we can manipulate the data from the temp table.
- We can use this, when we are doing large number of row manipulation in stored procedures. Remember, If we create a temp table inside the stored procedure, then it will be applicable to that SP only. It means, you can not call the temp table from outside the stored procedure.
Local Temp Table in SQL Server
The name of the Local temporary table starts with the hash (“#”) symbol, and it is stored in the tempdb. Local temporary tables are available only in the current connection. If the user disconnects from current instances, then they are automatically deleted. Or, If we close the Query Window then the table will be dropped.
Local Temp Table in SQL Server Syntax
The following code snippet will show you the syntax behind the local temporary tables in SQL
| CREATE TABLE #[Local Temp Table] ( Column_Name1 Data_Type(Size) [NULL | NOT NULL], Column_Name2 Data_Type(Size) [NULL | NOT NULL], Column_NameN Data_Type(Size) [NULL | NOT NULL] );
|
Here, Table Name of a local temporary table should start with #. Remember, Please provide Unique table name here. If you write the already existing table name, it will throw an error
Create Local Temp Table in SQL Server Example
We are going to use the below shown code to create local temporary table called LocalTemp.
SQL CODE
-- Creating Local Temp Table in SQL Server
CREATE TABLE #LocalTemp
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](255) NULL,
[LastName] [nvarchar](255) NULL,
[Occupation] [nvarchar](255) NULL,
[YearlyIncome] [float] NULL,
[Sales] [float] NULL
)
From the above code you can observe that, We declared 6 Columns. Here, Our first column is ID of Integer data type, and it will not allow NULL values. We also defined this column as Identity starting with 1 and incremented by 1.
OUTPUT
From the above screenshot you can observe that, Command is executed successfully. Please refresh the object explorer to see the Newly created Temporary Local table
Insert Data into Local Temp Table in SQL Server
Let me insert few random, or sample records into the local temporary table that we created inside the tempdb using the INSERT Statement.
SQL CODE
-- Inserting Values into Local SQL Temp Table
INSERT INTO #LocalTemp (
[FirstName], [LastName], [Occupation], [YearlyIncome], [Sales])
VALUES ('Bob', 'Ward', 'Finance', 20000, 300)
,('Anil', 'Jain', 'Software Professional', 30000, 200)
,('Scott', 'Ford', 'Trainer', 25000, 40)
,('Amit', 'Kumar', 'Hr Executive', 35000, 890)
OUTPUT
Select Data from Local Temp Table in SQL Server
From the above screenshot you can see that, we successfully inserted 4 random records into the #LocalTemp table. Let me use the SELECT Statement to select the records present in the temp table.
SQL CODE
| -- Selecting Values From Local SQL Temp Table SELECT [FirstName], [LastName], & Find MS SQL Development Training near you Learn from Best Tutors on UrbanPro. Are you a Tutor or Training Institute? The best tutors for MS SQL Development Classes are on UrbanPro The best Tutors for MS SQL Development Classes are on UrbanPro |
