What does 'select to a temp table' mean?

This answer had me slightly confused. What is a 'select to a temp table' a开发者_运维技巧nd can someone show me a simple example of it?

---

A temp table is a table that exists just for the duration of the stored procedure and is commonly used to hold temporary results on the way to a final calculation.

In SQL Server, all temp tables are prefixed with a # so if you issue a statement like

Create table #tmp(id int, columnA)

Then SQL Server will automatically know that the table is temporary, and it will be destroyed when the stored procedure goes out of scope unless the table is explicitly dropped like

drop table #tmp

I commonly use them in stored procedures that run against huge tables with a high transaction volume, because I can insert the subset of data that I need into the temp table as a temporary copy and work on the data without fear of bringing down a production system if what I'm doing with the data is a fairly intense operation.

In SQL Server all temp tables live in the tempdb datase.

See this article for more information.

---

If you have a complex set of results that you want to use again and again, then do you keep querying the main tables (where data will be changing, and may impact performance) or do you store them up in a temporary table for more processing. It's better to use a temporary table often.

Or you really need to iterate through rows in a non-set fashion you can use a temp table (or CURSOR)

If you do simple CRUD against a DB then you probably have no need for temp tables

You have:

• table variables: DECLARE @foo TABLE (bar int...)
• explict temp tables: CREATE TABLE #foo (bar int...)
• inline created: SELECT ... INTO #foo FROM...

---

A temp table is a table that is dynamically created by using some such syntax:

SELECT [columns] INTO #MyTable FROM SomeExistingTable

What you then have is a table that is populated with the values that you selected into it. Now you can select against it, update it, whatever.

SELECT FirstName FROM #MyTable WHERE...

The table lives for some predetermined scope of time, for example, for the duration of the stored procedure in which it lives. Then it's gone from memory and never accessible again. Temporary.

HTH

---

You can use SELECT ... INTO to both create a temp table and populate it like so:

SELECT Col1, Col2... 
INTO #Table
FROM ...
WHERE ...

(BTW, this syntax is for SQL Server and Sybase. )

EDIT Once you had created the table like I did above, you can then use it other queries on the same connection:

Select
From OtherTable
    Join #Table
        On #Table.Col = OtherTable.Col

The key here is that it all happens on the same connection. Thus, to create and use a temp table from a client script would be awkward in that you would have to ensure that all subsequent uses of the table were on the same connection. Instead, most people use temp tables in stored procedures where they create the table on one line and then use a few lines later in the same procedure.

---

Think of temp tables as sql variable of type 'table'. Use them in scripts and stored procedures. It comes handy when you need to manipulate data that is not simple value but a subset of a database table (both vertical and horizontal).

When you realize these benefits then you can take advantage of more power that comes with various sharing models (scope) for temp tables: private, global, transaction, etc. All major RDBMS engines support temp tables but there is no standard features or syntax for them.

For example of usage see answer.