SQL server creates two types of temporary tables: Writing and deleting temporary tables. SQL server

SQL server creates two types of temporary tables: Writing and deleting temporary tables. SQL server

-- Create and delete temporary tables

-- Method 1: create table # tmp (name varchar (255), id int) -- Method 2: select count (id) as storyNum, sum (convert (numeric ), case when isnumeric (code) = 1 then code else 0 end) as codeNum, sum (case when isnumeric (realcode) = 1 then convert (numeric (10, 2), realcode) else 0.0 end) as realcodeNum, tdtname, cycle, jiracomponent, jirastatename, qualityvalue, storycodellt into # tmp from multicast group by tdtname, cycle, jiracomponent, jirastatename, qualityvalue, storycodellt -- Query temporary table select * from # tmp -- delete temporary table if object_id ('tempdb .. # tmp ') is not nullbegindrop table # tmp end

How to delete a temporary SQL Server table

Deleting a temporary SQL Server table is not the same as that of a General table. The following section provides examples of errors and correct deletion operations for your reference.

A temporary table is different from a general table. It is saved to a tempDb table. The temporary table name is different from the table name you created, because it creates different temporary tables for the same operation of different people.

1. Incorrect delete operation:

-- The temporary table deletion operation is incorrect because the database WHERE the table is located is different from if exists (SELECT * FROM sysobjects WHERE object_id = OBJECT_ID (n' [dbo]. [# tempTable] ') AND type in (n'u') Begin drop table [dbo]. [tempTable] End -- the temporary table deletion operation is incorrect because the temporary table name has changed to if exists (select * from tempdb. dbo. sysobjects where id = object_id (n' [# temptable] ') Begin drop table # temptableEnd

2. Correct deletion method:

-- Correct temporary table deletion operation if object_id ('tempdb .. # temptable') is not null Begin drop table # tempTableEnd

SQL checks whether a temporary table exists and deletes the temporary table and recreates it.

IF Object_id ('tempdb .. # dl ') is not null drop table # dl -- if a temporary TABLE exists, delete create table # dl (neirong char (20), icount int, dlzonjine int, dlshu int, dlyin int) -- recreate the temporary table insert into # dl SELECT * FROM tab1 -- INSERT data FROM the physical table to the temporary table