Friday, May 26, 2017

SQL Server global temporary table and temporary table differences

create table ##t1 (id int);

declare @str1 nvarchar(100);
set @str1 = 'insert into ##t1 values (1)';
execute sp_executesql @str1

select * from ##t1



(1 row(s) affected)
id
-----------
1

(1 row(s) affected)

create table #t2 (id int);
declare @str2 nvarchar(100);
set @str2 = 'insert into #t2 values (2)';
execute sp_executesql @str2

select * from #t2


(1 row(s) affected)

id
-----------
2

(1 row(s) affected)

==============================



declare @str1 nvarchar(100);
set @str1 = 'create table ##t1 (id int);insert into ##t1 values (1)';
execute sp_executesql @str1

select * from ##t1


(1 row(s) affected)
id
-----------
1

(1 row(s) affected)


create table #t2 (id int);
declare @str2 nvarchar(100);
set @str2 = 'insert into #t2 values (2)';
execute sp_executesql @str2

select * from #t2


(1 row(s) affected)

Msg 208, Level 16, State 0, Line 14
Invalid object name '#t2'.