Create Temporary virtual table (or a records set) with SQL Server Stored Procedure

Some time we need to create a virtual table to manipulate data without affecting the actual data in the actual table. You can create a virtual table by two ways. Using CREATE TABLE statement and with DECLARE statement.

Let’s consider, I have student table(tblStudent) contain sName, sAddress, ClassId and class table(tblClass) contain classId and ClassName. Now I want to SELECT Name, Address and ClassName based on string pattern and Stored it to a virtual table.

First of all take a look at our table structure…

Create New table based on above structure and add some dummy data in it.

Temporary virtual table with CREATE TABLE statement

CREATE TABLE statement(command) is the most common way to create a Table on MS SQL Server Database. To create temporary virtual table with CREATE TABLE statement, we need prefixed the table name with a hash mark/pound sign/number sign (#). When you prefix # sign to "table name" with CREATE TABLE statement, SQL Server will create a local temporary virtual table. This temporary virtual table is only visible to this session of SQL Server. When I close this session, the temporary virtual table will be automatically get detele. This virtual table work same as the normal SQL Server database table, except you can’t have foreign key constraints on a temporary virtual table.

Take a look at SQL statement listed below. Which will create a temporary virtual table with CREATE TABLE statement and Stored some records to it and Display it.

CREATE TABLE #tblStudentDtl(
SrNo int IDENTITY not null,
sName varchar(50) ,
sAddress varchar(100),
ClassName varchar(100)
)
 
INSERT INTO #tblStudentDtl (sName,sAddress,ClassName)
SELECT sName, sAddress,ClassName FROM tblStudent A JOIN tblClass B ON A.ClassId=B.ClassId WHERE sName like +‘s%’ ORDER BY sName
 
SELECT * FROM #tblStudentDtl
 
DROP TABLE #tblStudentDtl

When you used CREATE TABLE statement, Temporary tables are created in tempdb. In a single active session of SQL Server, You can not used CREATE TABLE statement again and again, First you need to drop the existing temporary table. With DECLARE statement no Temporary tables are created in tempdb.

Temporary virtual table with DECLARE statement

If you are using SQL Server 2000 or higher, DECLARE statement is used to declare Variables in the body of a batch or procedure. After declaration, all variables are initialized as NULL, unless a value is provided as part of the declaration. We can assigned variables values by using either a SET or SELECT statement. We can define variable of type table with DECLARE statement. Variable names must begin with an at (@) sign and conform to the rules for identifiers.

Take a look at SQL statement listed below. Which will create a temporary virtual table with DECLARE statement and Stored some records to it and Display it.

DECLARE @tblStudentDtl TABLE(
SrNo int IDENTITY not null,
sName varchar(50) ,
sAddress varchar(100),
ClassName varchar(100)
)
 
INSERT INTO @tblStudentDtl (sName,sAddress,ClassName)
SELECT sName, sAddress,ClassName FROM tblStudent A JOIN tblClass B ON A.ClassId=B.ClassId WHERE sName like +‘s%’ ORDER BY sName
 
SELECT * FROM @tblStudentDtl

Conclusion:

You can use CREATE TABLE statement as well as DECLARE statement to create temporary virtual table (or a records set) with SQL Server Stored Procedure. If you need indexed from differnet different tables then you must use a temporary table.