SQL Learning notes--differences in how temporary tables are created

issue: when using temporary tables in a stored procedure, the Create temporary table does not cause an error.

This paper summarizes the differences between the methods of creating temporary tables.

There are two ways to create a temporary table:

Method One

CREATE TABLE #临时表名

(

Field 1 Constraints,
Field 2 Constraints,
......

)

CREATE TABLE # #临时表名

(

Field 1 Constraints,
Field 2 Constraints,
......

)

Method Two

SELECT * into #临时表名 from your table;
SELECT * into # #临时表名 from your table;

Note: The above # represents a local temporary table, # #代表全局临时表

Differences when used in stored procedures:

Sometimes SQL statements are stitched into a string in a stored procedure and then executed with exec, and if you create a temporary table in the second way in a string, you will get an error (the temporary table cannot be found), you can only create a temporary table in the first way and then insert the data.

Example:

1. Execute SQL statements directly, either way

1 Select  into  from test_student 2 Select *  from #tStudent

And

 1  create  table   #tStudent  2   ( 3  Name varchar  (100  )  4  )  5  6   Insert  into  #tStudent select  Name Span style= "COLOR: #0000ff" >from   test_student  7  select  *  from  #tStudent 

Execution results, such as

2. Splicing SQL statements into strings, you can only use the first method

1 Declare @outsql varchar (4000) 2 Set @outsql = ' Select SName to #tStudent from Test_student ' 3 exec (@outsql) 4 Select *  from #tStudent

This will be an error, as follows

1 Declare @outsql varchar(4000)2 Create Table#tStudent3 (4SNamevarchar( -)5 )6 Set @outsql = 'INSERT INTO #tStudent select Name from Test_student'7 exec(@outsql)8 Select *  from#tStudent

This can be done correctly, such as

SQL Learning notes-differences in how temporary tables are created