Check whether the temporary table exists

SQL Server checks whether a temporary table exists

  if   object_id('tempdb..##TEMPTABLE1')   is   not   null      drop   table   ##TEMPTABLE1    

The above type is not checked. # temptable1 may be the name of view or procedure,

The complete check is as follows:

if   exists   (select   *   from   tempdb.dbo.sysobjects   where   id   =   object_id(N'tempdb..##TEMPTABLE1')   and   type='U')     drop   table   ##TEMPTABLE1 

Considerations for handling temporary tables

It is difficult to use the select into temporary table. The following are some precautions for use (take MSSQLServer as an example ):

1, generally used

Create Table person (ID int, name varchar (20), birthday datetime)

Read data to the temporary table select * into # tb_tmp from person where ....
Use the data in the temporary table select name from # tb_tmp where id = 1
Do not forget to delete the temporary table (drop table # tb_tmp) after use; otherwise, an error will be reported for the next use, as shown in:
Message 2714, level 16, status 6, 1st rows
There is already an object named '# tb_tmp' in the database.

2. FAQs
Select ''as col into # tb_tmp
The following error is returned:
Message 2731, level 16, state 1, 2nd rows
Column 'col' has invalid width: 0.

There are two ways to solve the problem:
Solution 1, ''-> null
Select null as col into # tb_tmp
Solution 2: ''-> ltrim ('') or ''-> rtrim ('')
Select rtrim ('') as col into # tb_tmp

Solution 1:
Insert problem: Only int numbers or numeric strings can be inserted. (If digits with decimal digits are inserted, the decimal digits are automatically removed)
If a string other than a number is inserted, for example, insert into # tb_tmp values ('asdfsadf'), the following error is returned:
Message 245, level 16, state 1, 3rd rows
Syntax error converting the varchar value 'asdfsadf' to a column of Data Type Int.

Solution 2:
Insert problem: only strings with a specified number of spaces in the "('space number')" range can be inserted. If the number of strings is greater than the number of spaces specified in parentheses, the following error is returned:
Message 8152, level 16, status 9, 2nd rows
String or binary data wocould be truncated.
The statement has been terminated.

Conclusion: The problem caused by select into cannot be well solved. We recommend that you define the complete structure of the temporary table before using the temporary table, and add (collate

Database_default) attribute. Otherwise, garbled characters may occur when Chinese characters are used in some systems.

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/xddouble/archive/2007/10/11/1819606.aspx