Binaryworld - Create temporary or regular table with SELECT INTO ... [ SQL Server -> T-SQL ]

The SELECT INTO statement is a combination of the SELECT and INSERT T-SQL commands, that lets you create a new table from a subset of the rows and/or the columns of another table. The target table of this command is often a temporary table:

Click here to copy the following block

SELECT au_fname, au_lname INTO #authors_CA FROM authors WHERE State='CA'

You can also use SELECT INTO to create a new, non-temporary table, but in this case you must explicitly enable it, using the sp_dboption stored procedure:

Click here to copy the following block

EXEC sp_dboptions pubs, 'select into/bulkcopy', True

The SELECT INTO statement is very fast, for one reason: the command isn't logged for backup purposes. More precisely, the command can be inside a transaction and any rollback command will correctly undo its effects. However, the new values aren't permanently stored in the log file, therefore after this command you can only perform a complete database backup (incremental backup raise errors). This explains why you have to explicitly enable this functionality for non-temporary tables (temporary tables are never included in backup, so you don't need to use the sp_dboption command before using SELECT INTO with a temporary table).