SQL Server

SELECT INTO

The SELECT INTO statement enables one to create new table based on query results.

One can create both temporary and permanent tables using SELECT INTO.

Syntax:

SELECT column_list
INTO new_table_name
FROM table_list
WHERE search_condition

Example:

SELECT * into mystores
FROM Stores
Go

OR

SELECT title_id, title
INTO #tmptables
FROM titles
Go

• When Creating permanent tables using select into, one must set the SELECT INTO/BULK COPY database option true.
  
  [sp_dboption 'pubs', 'select into/bulkcopy', true]

• Name of the new table must be unique within the database and must conform to the rules for SQL Server naming conventions.

• When creating local temporary table (Local temporary table are those that are available only during the current user session to SQL Server and are delocated when the session is terminated) use in front of new table name.

• When creating global temporary table (Available to all user sessions to SQL Server and are deallocated when the last user session accessing the table is terminated) use ## in front of new table name.

>>>Next>>>