SQL basic operations--select into vs. temp table

The select into statement selects data from one table and then inserts the data into another table, often to create a backup copy of the table or to archive records.

--to make a backup copy of the "Persons" table:SELECT *  intoPersons_backup fromPersons--the IN clause can be used to copy a table to another databaseSELECT *  intoPersonsinch 'Backup.mdb'  fromPersons--if we want to copy some fields, we can list them after the SELECT statementSELECTLastname,firstname intoPersons_backup fromPersons--extracting information from the "Persons" table of people residing in "Beijing", creating a table with two columns named "Persons_backup"SELECTLastname,firstname intoPersons_backup fromPersonsWHERECity='Beijing'--It is also possible to select data from more than one table. Create a new table named "Persons_order_backup" that contains information from the Persons and Orders two tablesSELECTPersons.lastname,orders.orderno intoPersons_order_backup fromPersonsINNER JOINOrders onPersons.id_p=Orders.id_p

temporary table: its operation and our usual control table operation basically the same, such as the simplest increase, delete, change, check and so on. However, it is important to note that the creation of temporary tables is limited in scope.

Session Temp Table:

--at the end of the session, the temporary table is over. Create Table#TestTb (Idint Identity(1,1)Primary Key  not NULL, Namenvarchar( +)NULL)--usage of common temporary tablesSelect *  into#Tb fromT8--after you run out of temporary tables, be sure to: Release the temporary tableDrop Table#TestTb

Global temp Table:

--creates a global temporary table that all users ' sessions can access. Create Table#Pos (Idint Identity(1,1)Primary Key  not NULL, Namenvarchar( +)NULL)--The Global staging table is automatically freed after all users have disconnected the session. Select *  into# #Pos fromPosition--try not to be global. --releasing a global temporary tableDrop Table# #Pos

Reference: Use of temporary tables in SQL Server, SQL SELECT into statement

SQL basic operations--select into vs. temp table