We can create global
temporary tables but these are not using much in sql an the name of these table
start with two pound signs. For example, ##interviewqsn is a global temporary
table.As the name suggest these table is Global temporary tables and visible to
all SQL Server connections. When we create any one of these all users can see
it.
The syntax for creating the global temporary table is given below:
create table ##[table name] (col1 datatype, col2 datatype, ......coln datatype)
For example, the syntax to create the global temporary table ##employeedetails is given below:
CREATE table ##employeedetails (id int identity(1,1), empFname nvarchar(100), empEname nvarchar(100), empdate datetime)
We can also create indexes on the
Global temporary tables. For example,
Create index indx_GTT on ##employeedetails (empFname)
We can also define constraints on the Global temporary tables. For Example
Alter table ##employeedetails add constraint pk_GTT primary key(id)
Create index indx_GTT on ##employeedetails (empFname)
We can also define constraints on the Global temporary tables. For Example
Alter table ##employeedetails add constraint pk_GTT primary key(id)
Similar to
physical table and local temporary table, the values in the global temporary
table can also be inserted with the help of the Insert command. For example, to
insert the value in the global temporary table ##
employeedetails , we can use the following SQL Statement:
Insert into ## employeedetails ( empFname , empEname , empdate )
Values ( 'Roja', ' Samala', getdate())
We can access the global table within the stored procedure in which it is defined and also in the stored procedure which is called inside this stored procedure.
Create procedure test_GTT
as
begin
CREATE table ##employeedetails (id int identity(1,1), empFname nvarchar(100), empEname nvarchar(100), empdate datetime)
exec insert_GTT
select * from ##employeedetails
drop table ##employeedetails
end
Create procedure insert_GTT
as
begin
Insert into ##employeedetails ( empFname , empEname , empdate )
Values ( 'Roja', ' Samala', getdate())
end
Insert into ## employeedetails ( empFname , empEname , empdate )
Values ( 'Roja', ' Samala', getdate())
We can access the global table within the stored procedure in which it is defined and also in the stored procedure which is called inside this stored procedure.
Create procedure test_GTT
as
begin
CREATE table ##employeedetails (id int identity(1,1), empFname nvarchar(100), empEname nvarchar(100), empdate datetime)
exec insert_GTT
select * from ##employeedetails
drop table ##employeedetails
end
Create procedure insert_GTT
as
begin
Insert into ##employeedetails ( empFname , empEname , empdate )
Values ( 'Roja', ' Samala', getdate())
end
In the above example, we create a global temporary table ##employeedetails
in the stored procedure test_GTT. This procedure call another procedure insert_GTT. The procedure contains the Insert command for the table ##employeedetails. When we execute the procedure test_GTT, it will give the following result.
Exec test_GTT